Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
05-07-2011, 09:53 PM #1
- Join Date
- Apr 2011
- Thanked 0 Times in 0 Posts
Many-to-many relationships in one row
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!
Last edited by mingqi; 05-08-2011 at 12:16 AM.
05-07-2011, 10:12 PM #2
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 147 Times in 138 Posts
See GROUP_CONCAT or handle the output in your front end application.
Users who have thanked guelphdad for this post:
05-08-2011, 06:11 AM #3
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.An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Users who have thanked Old Pedant for this post: