Can't seem to get this right!

programming
1

Hi everyone,

I am a little stuck on this query and can’t quite work out how to do it. Basically, I need to output some results to an Excel file from my database using PHP - I’ve done the PHP bit, I just can’t get the MySQL to output the correct results.

I am getting data from three tables - Student, Choice and Unit.

The tables look something like -

Student

AdmissionNo----Firstname-----Surname
00001------------Jack-----------Smith
00002------------Jill-------------Smith

Unit

Unit_ID---------Name
00001-----------Maths
00002-----------Science

Choice

Choice_ID------Unit_ID--------AdmissionNo--------Preference
00001-----------00001---------00002----------------1
00002-----------00002---------00002----------------2
00003-----------00001---------00001----------------2
00004-----------00001---------00001----------------1

I want to achieve an output in my Excel file that looks something like -

Surname—|---Firstname—|---Preference 1—|---Preference 2
Smith-----------Jack------------Maths---------------Science

Using this SQL statement:

SELECT Surname, Firstname AS 'First Name', Name AS 'Preference 1' FROM student LEFT JOIN Choice ON student.AdmissionNo = Choice.AdmissionNo LEFT JOIN Unit ON Choice.Unit_ID = Unit.Unit_ID WHERE Preference = '1'

I get :

Surname—|---Firstname—|---Preference 1
Smith-----------Jack-------------Maths

But I can’t figure how to list the other preferences as rows. Any help is much appreciated!

Thank you.