Selecting for each row values from a related table

Question

I am rather new to SQL, and I need some help. Suppose I have two tables, Person (with columns PID and Name) and Visit (with columns PID (fk) and Date), where each Person can have multiple Visits.

I would like to select every person (with a condition, omitted here) with all the visit dates on the same row as the person they belong to, like

| PID |  Name  |   Date   |   Date   |   Date   |
| ----| -------|--------- |----------|----------|
|   1 | Daniel | 25/01/21 | 13/06/21 |          |
|   2 | Nicole | 26/01/21 | 18/06/21 | 07/10/21 |
|   3 | Kayla  | 02/02/21 | 25/06/21 |          |

I've tried

SELECT PersonID, Name (SELECT Date FROM Visit V WHERE V.PersonID = P.PersonID) FROM Person P

which obvisously doesn't work. MySQL says

#1242 - Subquery returned more than 1 row

which I by all means expected! How can I solve this?

can you share data from your input tables `Person` and `Visit`? — lemon, May 16 '22 at 22:42

score 0 · Accepted Answer · answered May 16 '22 at 23:05

This query will give you a bit different result (dates in one column), but it would be easy to parse if you need to:

SELECT person.PID,person.Name,group_concat(visit_date) as dates from person,visit where person.PID = visit.PID group by person.PID

It will be something like that:

+-----+-------+----------------------------------+
| PID | Name  | Dates                            |
+-----+-------+----------------------------------+
|   1 | Marek | 2022-05-15,2022-05-16,2022-05-12 |
|   2 | Magda | 2022-05-16,2022-05-16,2022-05-16 |
+-----+-------+----------------------------------+```

Selecting for each row values from a related table

1 Answers1