SELECT loop within SELECT loop

Question

I have a PHP/MySQL scenario where I want to display a list of events but also show all users assigned to an event. In MySQL I have an events, eventusers and users tables.

I can SELECT event_id, event_name FROM events then loop through results in PHP and then SELECT user_id FROM eventusers WHERE event=[event_id] in each result loop to get that particular event's users.

I am wondering if there is way to do this in one single SQL query (and it's also performance decent) so I get data from the events table as well as all the user IDs attached to the event from the eventusers table. Something like:

event id | event name    | Users from eventusers table
------------------------------------------
1        | Soccer match  | 3,56,79
2        | Cycling       | 46,77,88,126,78

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat — Paul Spiegel, Mar 13 '18 at 22:10
This is not about joins. It is about being able to retrieve a list of results from a related table in each result row from the main table. I have improved the question to be more clear. — lavirius, Mar 13 '18 at 22:31

score 1 · Answer 1 · answered Mar 13 '18 at 22:14

1

You should make use of a JOIN statement, which merges your tables:

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

In your case, you should do something like:

SELECT event_name, user
FROM events
INNER JOIN eventusers
ON events.event_id = eventusers.event_id

I hope this is helpful for you

answered Mar 13 '18 at 22:14

Thomas Timmermans

342
3
21

Please see rephrased question – lavirius Mar 13 '18 at 22:32

score 0 · Answer 2 · answered Mar 13 '18 at 22:20

You can use the join function:

select * from
table1 JOIN table2
ON table1.pk=table2.fk

and then add the condition

where table1.column= something

however you need to have both tables joined with a column (.pk and .fk), in this case it could be the eventuserid

select * from
events JOIN eventusers
ON events.eventuserid=eventusers.userid
where event.id= [event_id]

Also you can subtitue the * with the columns you'll need: tablename.column and youll only get those column values in return

I'm sorry I dont understand the second part about the CSV file, can you clarify?

SELECT loop within SELECT loop

2 Answers2