One parent row, multiple child rows in another table. How to get them all in one row?

Question

I volunteer for a local non-profit that gives out toys and food during the holiday season. One way that I am helping them is by putting their gift/food requests online. This way instead of manually entering 1,000 or so names manually into Excel over the course of 15 hours they can simply click on a link and have the whole thing exported in the matter of seconds.

My issue is related to how I can get the data from the database in one query. I believe it is possible but can't quite wrap my head around I can do it.

There is a parent table which contains the family information for the kids who will receive gifts (1 row per family). There is a child table that contains the family's childrens' information (1 row per child, many rows for each parent row). There will be 1-3 children rows for each parent row.

PARENT TABLE
-----------------------------
id
name
address
city

CHILDREN TABLE
-----------------------------
parent_id
name
gift1
gift2
gift3

I would like to get all of the childrens' information with the parents' information in one row when exporting the data from the database so it can be put into a CSV file. I know I can do this with two separate queries but I don't want to hit the database for each and every family's children if possible.

I've pondered this for an hour or so and can't quite come up with how I would accomplish this. I'm sure I need to join the tables but I am unsure how to differentiate each child and avoid duplicating them instead of getting the next child (if there is one).

Answer 1

Since there is a variable number of children per parent, the only way it can be done in SQL is presenting all gifts semicolon separated in a single record:

SELECT  p.*, GROUP_CONCAT(c.name, ': ', c.gift1, ', ', c.gift2, ', ', c.gift3 SEPARATOR ';')
FROM    parents p
JOIN    children с
ON      c.parent_id = p.id
GROUP BY
        p.id

This will give you something like this:

parent      children_and_gifst
Mr. Jones   Alice: teddy bear, candy, drum; Bob: pup, sword, flute

, all children and their gifts in one column.