View Full Version : Many-to-many relationships in one row

05-07-2011, 10:53 PM
Hi! I was wondering if the following would be possible:

I have a table called professors which has a many-to-many relationship with a table called courses, since a professor can teach more than one course. When I try to do a mysql query for a professor and the courses, I come up with the something like this:

Professor A GEO432
Professor A BIO101
Professor A PHY302
Professor B GEO432
Professor B SCI222
Professor B ECO154

When I want something like this:
Professor A GEO432 BIO101 PHY302
Professor B GEO432 SCI222 ECO 154
I want everything under Professor A to be in one row.

I tried the GROUP-CONCAT method, but it doesn't work very well with the application I am developing - part of which includes a search of the database. Depending on the field search, I either get all NULLs or all subjects possible in the table get concat-ed..seems like it is intent to have everything in one row when I just want each single professor to have his own row instead of multiple rows....i.e.
Searching for GEO 432
I would get
Professor A GEO 432, GEO 432 [<---instead of going to the next row to say Professor B teaches GEO 432 also, I get the subject repeated in the same row]

I digress...so anyways, I want to find another way. The case I am providing is just an simplified example of what I am trying to do. Hopefully that is enough information. Thank you!

05-07-2011, 11:12 PM
See GROUP_CONCAT or handle the output in your front end application.

Old Pedant
05-08-2011, 07:11 AM
If you don't show us the queries you are using, we can't show you what you are doing wrong.

But be aware that Group_Concat will return all the courses taught (in your example case) in a *single* field, and often that makes manipulation by the PHP (or ASP or JSP or whatever you are using) page harder than simply doing the aggregation in the client language.

Also, if you are searching for a *single* course, then there's no reason to use GROUP_CONCAT.

There's no rule that says you must use the same query for all cases.

When you WANT multiple courses per professor, use GROUP_CONCAT. When searching for a single course, don't.