MySQL fetch parent and all children

Question

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

@Barmar 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 — Jamie, Nov 23 '15 at 17:44
I have parents which might have children with 3 children of the same name but maybe 1 child different - I don't want a query with 3 children name to return the parent with 4 children. — Jamie, Nov 23 '15 at 18:03
Can you give us an additional example demonstrating this exact case? — Giorgos Betsos, Nov 23 '15 at 18:11
Consider the following: `SELECT c.parent, COUNT(*)a, SUM(CASE WHEN c.name IN ('Jack','Bob','Smith') THEN 1 ELSE 0 END)b FROM child c GROUP BY parent;` - and note that you appear to have redundancy in your data (the value column, which perhaps would be better placed in a separate table) — Strawberry, Nov 23 '15 at 18:25

score 1 · Accepted Answer · answered Nov 23 '15 at 17:32

1

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.

answered Nov 23 '15 at 17:32

Giorgos Betsos

71,379
9
63
98

But what if you search for Jack Smith Bob. This soluition is icky – Strawberry Nov 23 '15 at 17:59
My query is doing an `ORDER BY` in `GROUP_CONCAT`. So it will work as long as the string literal contains names in an ascending order. – Giorgos Betsos Nov 23 '15 at 18:00
I'm talking about the right hand side of the WHERE clause – Strawberry Nov 23 '15 at 18:00
@Strawberry It works because the OP is looking after an *exact* match: `'Bob,Jack,Smith'` cannot be written in any other way if names are ordered. – Giorgos Betsos Nov 23 '15 at 18:03
I don't think the OP's latest comment above concurs with that assessment – Strawberry Nov 23 '15 at 18:05

MySQL fetch parent and all children

1 Answers1