Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    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 01:16 AM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    St. Catharines, Ontario Canada
    Thanked 148 Times in 139 Posts
    See GROUP_CONCAT or handle the output in your front end application.

  • Users who have thanked guelphdad for this post:

    mingqi (06-03-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,732 Times in 4,694 Posts
    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:

    mingqi (06-03-2011)


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts