Returning Multiple Rows Inside a Single Row (MySQL 5)

[lightnb]

Assuming the following table structure:

Table "Schools"
------------------
ID
SchoolName

Table "Users"
------------------
ID
UserName


Table "UserSchools"
------------------
SchoolID
UserID

How can I get a result row that has the user's name, and all of their school's names, with a single query, using MySQL (version 5.0.38)?

Say for example that I wanted to print a list of users, followed by their school(s).

The way I'm doing it now would require me to first run a query to get all the users and their ids, and then run another query (in a loop) for every user to get their schools. That means if there's 100 users shown, I have to run 101 queries!

Can someone tell me a more effective way to do this?

[chazzy]

The short answer is that you can't.

The long answer is that you can't because it's a bad design path. sql works because it's deterministic in nature, you can get rows of result back. If I had to guess, the reason why you want it to put everything into one row is so that you don't have to figure out if it's the same user or a different user.

As for your real dillemma, I think this should solve your problems.



select
u.UserName,s.SchoolName
from users u
inner join userschools us on u.ID=us.UserID
inner join schools s on s.ID=us.UserSchools
order by UserName asc, SchoolName asc;


This returns all users paired up with a school of theirs.

AS for the other issue "I only want to print out the user name once" which I'm sure is going to come next, this is easy enough to do. Keep track of the last username you printed out, if it's different than the one before it, print it out and switch.