Wednesday, December 2, 2009

Where And Means Or and Or means And.

Have you ever found yourself sitting around wondering what exactly it is I do all day at work when not giving you this lovely typo ridden blogs? No? You have haven't...well I gotta say that hurts. Lucky for you I don't care. I am going to tell you any ways. Here is a stark lesson in something I learned long ago and that bit me and a co-worker in the ass yesterday.

So we all know the words And and Or. And means these two things in conjunction. Or means this thing or this other thing. For the most part this logic holds true in computers as well. Save for the situation I am about to show you. Now you need not know a lot about SQL to get this lesson. The part you want to pay attention to is in Italics.

SELECT COUNT(*) AS rowCount
FROM StudentCourse r,
(SELECT StuCrs_Student, StuCrs_Course, StuCrs_LicenseCourse, StuCrs_Completion, MAX(StuCrs_Created) AS maxTimestamp
FROM StudentCourse
WHERE StuCrs_LicenseCourse = 'Course'
AND StuCrs_LicenseCompletion = '1974-10-17'
AND StuCrs_LicenseStudent = 'Student'
AND (StuCrs_Course <> StuCrs_LicenseCourse
AND StuCrs_Completion <> StuCrs_LicenseCompletion
AND StuCrs_Student <> StuCrs_LicenseStudent)
GROUP BY StuCrs_Student, StuCrs_Course, StuCrs_Completion) rh

WHERE r.StuCrs_Student = rh.StuCrs_Student
AND r.StuCrs_Course = rh.StuCrs_Course
AND r.StuCrs_Completion = rh.StuCrs_Completion
AND r.StuCrs_Created = rh.maxTimestamp

See that inner Select statement is how I am getting a ton of items out of our database. Now the symbole which you are likely not familiar with is "<>". That simply means not equal. Why did SQL chose that over != like every other reasonable language? Fuck if I know.

So here is where my cohort and I got into trouble, see if your saying this does not equal this and this does not equal this, and you only need one of them not to be equal you actually want to say OR. Basically this happens b/c I am mixing negative statements (not equal) with positive statements (equals) I know this sounds obvious, but I promise you this trips up more developers then you know. So remember kids, sometimes when you mean And you really mean Or.

No comments: