2

I have seen this asked before in different ways, but I'm hoping for a straight answer.

If I have two classes, Parent and Child, stored in two different MySQL tables, what is the best way of querying for a complete Parent object, with a list of all Children?

e.g.

class Parent {
   id: number;
   name: string;
   Children: Child[]
}

class Child {
   id: number;
   name: string;
   age: number;
}

How do I get a list of matching Parents by name, with a list of children for each?

Do I have to write a query to grab all matching Parents, and then an additional query for each Parent to get all the Child elements?

This is obviously pseudo-SQL, but I'm hoping for something like this:

SELECT p.id, p.name, 
(SELECT * from c) as children from parent p LEFT JOIN child c on p.id = c.parent_id
tcmoore
  • 1,071
  • 1
  • 12
  • 29

2 Answers2

2

A subquery in the select-list like you show in your pseudocode won't work because a subquery in that context must be a scalar subquery—in other words, it must return only one row and one column.

You can return a joined result set:

SELECT p.id, p.name, c.id, c.name
FROM parent p LEFT JOIN child c on p.id = c.parent_id;

But that will repeat the same p.id and p.name for each of the rows. Some developers find that hard to deal with (see my answer in How to separate data in SQL table)

The alternative is to do two queries: 

SELECT p.id, p.name FROM parent p;

SELECT c.id, c.name FROM child c WHERE c.parent_id = ?;

Supply the p.id value as the parameter to the second query.

Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
0

If your parent table container child_id then

SELECT p.id, p.name,c.name,c.age from parent p LEFT JOIN child c on p.child_id = c.id

if your child table contain parent_id then

SELECT p.id, p.name,c.name,c.age from parent p LEFT JOIN child c on p.id = c.parent_id

in case a parent has multiple children then 

select parent.name,(select group_concat(name,'|',age) from child where child.parent_id = parent.id) as child  from parent
A.D.
  • 2,257
  • 2
  • 13
  • 24