CODEWITHSUNDEEP
mysql
Jamie
Jamie

Reputation: 724

MySQL fetch parent and all children

My database looks something like:

Parent:

+----+-------+
| ID | Name  |
+----+-------+
|  1 | Stage |
|  2 | Prod  |
+----+-------+

Children:

+-------+-------+--------+
| Name  | Value | Parent |
+-------+-------+--------+
| Bob   |    10 |      1 |
| Smith |     5 |      1 |
| Jack  |     3 |      1 |
| Bob   |    10 |      2 |
| Smith |     5 |      2 |
| Jack  |     3 |      2 |
| Black |     2 |      2 |
+-------+-------+--------+

How could I query for the parent by using the different children name as part of my query?

If I wanted to get parent ID 1 and all of the children for the parent how could I use "Bob", "Smith" and "Jack" in the query to get parent ID 1, but not parent ID 2.

Edit: I don't think this is a duplicate because the other question's solution would match both parent ID 1 and 2, due to them having the same children apart from 1

Upvotes: 0

Views: 383

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use GROUP_CONCAT:

SELECT ID, Name
FROM (
  SELECT p.ID, p.Name, GROUP_CONCAT(c.Name ORDER BY c.Name) AS children
  FROM Parent AS p
  INNER JOIN Children AS c ON p.ID = c.Parent
  GROUP BY p.ID, p.Name) AS t
WHERE t.children = 'Bob,Jack,Smith'

This will return any parents having exactly these ('Bob,Jack,Smith') children.

Upvotes: 1

Related Questions