Dodgy MySQL Query

[bowenbowen]

[code]
SELECT s.first_name AS 'First Name',
       s.last_name AS 'Last Name',
       s.year AS 'Year',
       s.form_group AS 'Form Group',
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.re = '1'
       AND s.student_id = b.student_id) AS RE,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rw = '1'
       AND s.student_id = b.student_id) AS RW,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rc = '1'
       AND s.student_id = b.student_id) AS RC,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rb = '1'
       AND s.student_id = b.student_id) AS RB,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d1 != ''
       AND s.student_id = b.student_id) AS D1,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d2 != ''
       AND s.student_id = b.student_id) AS D2,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d3 != ''
       AND s.student_id = b.student_id) AS D3,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d4 != ''
       AND s.student_id = b.student_id) AS D4
FROM students s
     INNER JOIN behaviour_record_sheets_archive b ON s.student_id =
     b.student_id WHERE s.year = '7'
GROUP BY s.student_id
ORDER BY s.last_name ASC;
[/code]

This query counts columns in the behaviour_record_sheets_archive table to see if there is anything in that column, then relates the student_id in the behaviour_record_sheets_archive table (stupid name I know, I didn't name it!) to the student_id in the student table . Basically I need to use the result of of the count statements in the WHERE clause.

I only want to display information where the count statements = 0.

Not sure if any of you will understand what I mean, but any help would be much appreciated.

[bowenbowen]

[!--quoteo(post=387105:date=Jun 23 2006, 10:20 AM:name=bowenbowen)--][div class=\'quotetop\']QUOTE(bowenbowen @ Jun 23 2006, 10:20 AM) [snapback]387105[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]
SELECT s.first_name AS 'First Name',
       s.last_name AS 'Last Name',
       s.year AS 'Year',
       s.form_group AS 'Form Group',
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.re = '1'
       AND s.student_id = b.student_id) AS RE,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rw = '1'
       AND s.student_id = b.student_id) AS RW,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rc = '1'
       AND s.student_id = b.student_id) AS RC,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rb = '1'
       AND s.student_id = b.student_id) AS RB,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d1 != ''
       AND s.student_id = b.student_id) AS D1,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d2 != ''
       AND s.student_id = b.student_id) AS D2,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d3 != ''
       AND s.student_id = b.student_id) AS D3,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d4 != ''
       AND s.student_id = b.student_id) AS D4
FROM students s
     INNER JOIN behaviour_record_sheets_archive b ON s.student_id =
     b.student_id WHERE s.year = '7'
GROUP BY s.student_id
ORDER BY s.last_name ASC;
[/code]

This query counts columns in the behaviour_record_sheets_archive table to see if there is anything in that column, then relates the student_id in the behaviour_record_sheets_archive table (stupid name I know, I didn't name it!) to the student_id in the student table . Basically I need to use the result of of the count statements in the WHERE clause.

I only want to display information where the count statements = 0.

Not sure if any of you will understand what I mean, but any help would be much appreciated.
[/quote]

Found a solution for it, me being dumb.

Although I would be interested to see if there is a better way.


[code]

SELECT s.first_name AS 'First Name',
       s.last_name AS 'Last Name',
       s.year AS 'Year',
       s.form_group AS 'Form Group',
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.re = '1'
       AND s.student_id = b.student_id) AS RE,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rw = '1'
       AND s.student_id = b.student_id) AS RW,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rc = '1'
       AND s.student_id = b.student_id) AS RC,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rb = '1'
       AND s.student_id = b.student_id) AS RB,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d1 != ''
       AND s.student_id = b.student_id) AS D1,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d2 != ''
       AND s.student_id = b.student_id) AS D2,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d3 != ''
       AND s.student_id = b.student_id) AS D3,
       (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d4 != ''
       AND s.student_id = b.student_id) AS D4
FROM students s
     INNER JOIN behaviour_record_sheets_archive b ON s.student_id =
     b.student_id WHERE
     (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d1 != '' AND s.student_id = b.student_id) = 0 AND
     (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d2 != '' AND s.student_id = b.student_id) = 0 AND
     (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d3 != '' AND s.student_id = b.student_id) = 0 AND
     (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d4 != '' AND s.student_id = b.student_id) = 0 AND
     s.year = 7
GROUP BY s.student_id
ORDER BY s.last_name ASC;[/code]

[fenway]

You could have used a HAVING clause, too.