MySQL Forums
Forum List  »  Newbie

Pulling my hair out with this query
Posted by: Reo Lucas
Date: August 11, 2005 03:38PM

Hello all. 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!

Options: ReplyQuote


Subject
Written By
Posted
Pulling my hair out with this query
August 11, 2005 03:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.