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.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
