MySQL query return NULL value for no record?

[Joseph_Sliker]

Here I go again with the NULL stuff.

I would like a MySQL query to return a NULL value for a specific record...when the record does not exist.

Say I have a table with kid's test scores for different seasons.

"SELECT PHASE, SCORE FROM DATATABLE WHERE STUDENTID=1234 AND (PHASE='FALL' OR PHASE='WINTER')"

If there is no record for this kid for FALL, the result will be something like:

+-----------+----------+
| PHASE | SCORE |
+-----------+----------+
|WINTER | 62 |
+-----------+----------+
1 rows in set (0.01 sec)

I believe there is MySQL syntax that would allow me to return the result that I want which would be this:

+-----------+----------+
| PHASE | SCORE |
+-----------+----------+
|FALL | NULL |
|WINTER | 62 |
+-----------+----------+
1 rows in set (0.01 sec)

😕

[AstroTeg]

The only thing I can think of is a table design tweak might get your results you desire. By default:

SELECT PHASE, SCORE FROM DATATABLE WHERE STUDENTID=1234 AND (PHASE='FALL' OR PHASE='WINTER')

Will tell the DBMS to just return all records where there's ID 1234 and the phase is fall or winter. It doesn't care if one of the phases is missing.

The table design tweak I'm thinking of is setting up another table to keep track which phases a student is a member of (or participates in). You could then left join these tables together and if there's no scores, you'd get a null result.

The only other thing I can think of is getting into subselects and if then structures...