question

sqltiger avatar image
sqltiger asked

How do you tell a SQL 2008 query to ignore certain rows?

I've run a query bringing in everything to see what I want to filter out. I need to show all employees in a particular division. Then I need show who has taught a particular class and who has not. Basically easy until you find screwy data. I need to get my query to ignore this screwy data. Following is a sample result set. The first User 2 row is the screwy data that I don't want to see. ![alt text][1] Here is my case statement that does work, but everything I've tried keeps giving the screwy data lines the 'Y' value. CASE WHEN TABLE2.COURSE_TITLE IS NOT NULL THEN'Y' WHEN TABLE1.ROLE = 'Teacher' AND TABLE2.COURSE_TITLE IS NULL THEN --... i don't want to see these rows returned at all. How do i tell it to ignore these? WHEN TABLE1.ROLE IS NULL THEN'N' END AS 'TAUGHT' Thank you! [1]: /storage/temp/753-ignore.jpg
nullcase-statement
ignore.jpg (27.6 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Fatherjack avatar image
Fatherjack answered
Your TSQL seems to point to more than one table being involved (Table1 and Table2 are mentioned) but we dont see how you are joining them or any other information - we will need more details about the tables to be more accurate with our answers. You should filter rows out of a recordset with the WHERE clause. What makes row 2 in your sample data a row that you dont want? If it is simply where a teacher has a NULL CourseTitle then you WHERE clause might be WHERE [NOT] (Table1.Role 'Teacher' and Table2.Course_Title IS NULL) with this filtering in the WHERE you should then simply use the Taught column values without the CASE statement
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Beandon10 avatar image Beandon10 commented ·
I think the where clause would be better if it was setup as WHERE NOT (Table1.Role = 'Teacher' AND Table2.Course_Title IS NULL). This would then pull in both first and third example records from above, but would ignore the second.
2 Likes 2 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
Thanks, good catch
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.