ekaf ekaf - 4 months ago 10
SQL Question

how to make MYSQL select query for one to many for this case?

create table person {
id int,
name char(10),
primary key(id),
}


create table person_child {
id int,
name char(10),
primary key(id),
foreign key(id) references person(id));
}


Can a person have 1...n child so it is one to many child . my query is to find the name of person with number of child he/she have?
what will be the select statement

Answer

Use join and group by

select person.name, count(*) as num_of_child
from person 
inner join person_child on person.name = person_child.name
group by person.name;