MySQL query How many children does Berenice have?

Navigation
3

This is the Database and what I want to know is how to ask how many children Berenice has 

create database padres_hijos;
use padres_hijos;

create table padres(padre_id varchar (20) primary key,
nombres varchar(30), apellidos varchar (30))engine=innodb;

create table hijos(
hijo_id varchar (20) primary key,
nombres varchar(30),
edad int(30),
padre_id varchar(20),
foreign key (padre_id) references padres (padre_id)
on update cascade on delete cascade)engine=innodb;

insert into padres values("L-001","Luis","Barrientos Lópéz");
insert into padres values("B-002","Berenice","Cerezo Moreno");
insert into padres values("C-003","Cesar","Centeno Núñes");
insert into padres values("P-004","Paty","Juárez Fenández");

insert into hijos values("G-001","Guillermo",18,"P-004");
insert into hijos values("H-002","Humberto",25,"C-003");
insert into hijos values("K-003","Karen",14,"B-002");
insert into hijos values("R-004","Rosita",20,"B-002");    
insert into hijos values("X-005","Xiomara",10,"L-001");
    
asked by Bellatrix Lestange 14.10.2018 в 05:30
source

2 answers

2

I tell you that it should stay like this

INQUIRY 

SELECT padres.nombres, COUNT(hijos.nombres) as Total
FROM padres
JOIN hijos
ON padres.padre_id = hijos.padre_id
WHERE padres.nombres = 'Berenice';

RESULT 

+----------+-------+
| nombres  | Total |
+----------+-------+
| Berenice |     2 |
+----------+-------+

WHAT I DID

  • make a COUNT() on the column of the names in the children table
  • I made a JOIN with the children table
  • Look for those records that were equal by means of the primary key padre_id in the parent table, with the foreign key padre_id in the children table
  • Finally with a where at the end, indicate that you only do the counting where the father's name is equal to berenice

    • UPDATE

    If you want, for example, to show you the number of children but for each parent, you only need to use the statement GROUP BY 

    SELECT padres.nombres, COUNT(hijos.nombres) as Total
FROM padres
JOIN hijos
ON padres.padre_id = hijos.padre_id
GROUP BY padres.nombres;
+----------+-------+
| nombres  | Total |
+----------+-------+
| Berenice |     2 |
| Cesar    |     1 |
| Luis     |     1 |
| Paty     |     1 |
+----------+-------+
        
    answered by 14.10.2018 / 05:40
    source
    0

    If you only want to know the amount, you do it with a count () 

     select count(h.hijo_id) from hijos h
 inner join padres p on (p.padre_id = h.hijo_id)
 where p.nombres = 'Berenice'
        
    answered by 14.10.2018 в 05:39
    Is it possible to display notifications with custom icon in Windows 10 with Java? REST Jersey + jackson servlet parsea part of the json as a JsonObject