Query to show null rows as well as found data?

Joseph_Sliker

Hi;

I thought this would be simple but I am quite mystified right now.

Say I've got a list of student IDs in one table. (STUDENTS)

Then I've got some testing records in another (PROTOCOLS)

I want to show the complete student list, and also see the testing data for those students who have been tested (leave the data columns blank for the other kids)

Now, I do need some filtering of the data as there are various test types and phases that I want to limit the query on.

I have been trying all kinds of joins all day and ...bupkis.

Here is basically what I need:

SELECT
STUDENTS.studentID,
PROTOCOLID
FROM STUDENTS
LEFT JOIN PROTOCOLS ON STUDENTS.studentID=PROTOCOLS.studentID
WHERE
PROTOCOLS.COLLAPSE='FALL' AND
PROTOCOLS.TEST='PALS' AND

PROTOCOLS.TESTPHASE LIKE 'J%'
GROUP BY studentID, PROTOCOLS.PROTOCOLID
ORDER BY studentID

This works except for the fact that the rows where PROTOCOLID should be empty (null) simply do not show up at all. I only get rows where data actually exists in the PROTOCOLS table.

I have not worked with subqueries and am not clear on use of the HAVING syntax. Would these apply to this problem?

Thanks.

djjjozsi

then add the is null as a cryteria:

SELECT students.studentid, protocol.protocolid
FROM students LEFT JOIN protocol ON students.studentid = protocol.userid
WHERE 
(
((protocol.COLLAPSE)="FALL") AND ((protocol.TEST)="PALS") AND ((protocol.TESTPHASE) Like 'j%')) 
OR
(
((protocol.COLLAPSE) Is Null) AND ((protocol.TEST) Is Null) AND        ((protocol.TESTPHASE) Is Null)
)
GROUP BY students.studentid, protocol.protocolid;

Joseph_Sliker

Thanks for your reply, but that still does not do it. I think the problem is that the protocol table has lots more fields than the ones used in the WHERE clause. I still only get records where data exists: I don't see blank lines with student IDs where the data has not yet been entered.

I thought there would be a nice, elegant MySQL query answer for this, but I suspect I am going to have to do this with .php: Maybe run one query to just get the kid ids, and loop through these...and echo an empty row in the displayed table if there is no data and the filled row if the data exists. That I know how to do, but it would be a lot more elegant if it could all be handled in the query.