CODEWITHSUNDEEP
mysqlone-to-many
ekaf
ekaf

Reputation: 153

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

Upvotes: 0

Views: 40

Answers (2)

Nvn
Nvn

Reputation: 21

Select p.name,count(PC.id) from person as p Inner join person_child as PC on p.ID=PC.ID Group by p.name This would do it

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

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;

Upvotes: 2

Related Questions