Jump to content


Photo

Dodgy MySQL Query


  • Please log in to reply
2 replies to this topic

#1 bowenbowen

bowenbowen
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationIpswich, Suffolk, UK

Posted 23 June 2006 - 09:20 AM

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;

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.

#2 bowenbowen

bowenbowen
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationIpswich, Suffolk, UK

Posted 23 June 2006 - 10:54 AM

[!--quoteo(post=387105:date=Jun 23 2006, 10:20 AM:name=bowenbowen)--][div class=\'quotetop\']QUOTE(bowenbowen @ Jun 23 2006, 10:20 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
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;

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.



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;


#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 June 2006 - 07:50 AM

You could have used a HAVING clause, too.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users