Shantanu's Blog

MySQL Forums :: Newbie :: Pulling my hair out with this query:
I have a PHP/MySQL database that has student grade data. Every student has at least one row of data, though they may have up to 3 rows depending on how many years they have been enrolled (each year gets one row).

Here is a very simplified view:

NAME|YEAR|TEST1|
------|------|-------|
John-|2002|---80|
Anna-|2002|---67|
John-|2003|---78|
Bill---|2003|---76|
Anna-|2003|---77|
John-|2004|---88|
Bill---|2004|---77|
Phil--|2004|---76|

*Note that John is there for all 3 years, Anna for only the first two, Bill for only the last 2, and Phil came in only in the last year.

The data needs to be displayed on the web page ordered by name with the 3 years in columns exactly like this:

Name|2002|2003|2004|
------|------|------|------|
Anna-|--67|--77--|null|
Bill---|null-|--76--|--77|
John-|--80-|--78-|--88|
Phil--|null-|null---|--76|

Currently I created 4 dataset queries like this:

'Select distinctrow NAME from mydata ORDER BY NAME'; //returns all names
'SELECT * FROM mydata WHERE `YEAR` = 2002 ORDER BY NAME';
'SELECT * FROM mydata WHERE `YEAR` = 2003 ORDER BY NAME';
'SELECT * FROM mydata WHERE `YEAR` = 2004 ORDER BY NAME';

I then use the result set to populate the columns.

The problem is that since not every student has data in all 3 years, the columns begin to shift and the data is placed in the incorrect row like this:

Name|2002|2003|2004|
-------|-----|------|-----|
Anna-|--67-|--77-|--77|
Bill---|--80-|--76-|--88|
John-|------|--78-|--76|
Phil--|------|null--|----|

Is there a way to create ONE query to return data for all of the students, but insert null values where there is no data? Is this done through a 'join' statement? What's the best approach?

Please help, I'm pulling my hair out!