#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Location
    NY
    Posts
    8
    Rep Power
    0

    How to combine multiple results into one


    Hello,
    I searched this forum but I am not sure the right words to describe the problem and so I have not been successful in seeing if this has been answered before. That being said I have a question about a selection that would return multiple rows. I want to combine the result into one result.

    Let me give you an example:
    Let's say I have 3 tables.
    Code:
    
          Student                    Class                   RelStudentClass 
    +----------+-------+     +----------+-----------+   +-----------+---------+ 
    |studentID | Name  |     |  classID | className |   | studentID | classID | 
    +----------+-------+     +----------+-----------+   +-----------+---------+ 
    |    01    | Mike  |     |     01   | Math 1    |   |    01     |    02   | 
    |    02    | John  |     |     02   | English 1 |   |    01     |    03   | 
    |    03    | Susan |     |     03   | Science 1 |   |    02     |    01   | 
    |    04    | Mary  |     |     04   | Math 2    |   |    02     |    02   | 
    |     .    |   .   |     |     05   | English 2 |   |    02     |    03   | 
    |     .    |   .   |     |     .    |     .     |   |    03     |    04   | 
    |     .    |   .   |     |     .    |     .     |   |     .     |     .   | 
    etc.
    and then I do a normal query like so....
    Code:
    SELECT st.Name, cl.className 
    FROM Student st 
    INNER JOIN RelStudentClass rsc ON st.studentID = rsc.studentID 
    LEFT JOIN Class cl on cl.classID = rsc.classID
    It would return something like this.
    Code:
    
    Mike     English 1
    Mike     Science 1
    John     Math 1
    John     English 2
    John     English 3
    
    NOW here is the question.
    Is there a way to change the MySQL query to produce something like this?
    Code:
    NAME          Math 1    English 1    Science 1    ....
    Mike                       X             X
    John            X          X             X
    etc.
    I'm guessing it could be done with if() statement? Like
    if (cl.className='Math 1', 'y','n')

    But if I do this for each class, then I would get multiple rows again. Is there a way? or do I have to go into PHP and create an array and handle it that way?
    I would like to do it all in MySQL if possible.
    Thank you.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    That is something you should do in your application code. As far as I know, there is no efficient or scalable way of writing a query that would do something like that.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Location
    NY
    Posts
    8
    Rep Power
    0
    Thanks, I was hoping there was a way......

    I guess I will make something like

    Make an double array[total student, total class]
    while (student name is same)
    add the class name to to total class



    Originally Posted by E-Oreo
    That is something you should do in your application code. As far as I know, there is no efficient or scalable way of writing a query that would do something like that.

IMN logo majestic logo threadwatch logo seochat tools logo