Excluding in a query with multiple conditions?

Question

Post reply

Excluding in a query with multiple conditions?

thehollis

Say Hey Kid

Points: 698
More actions
April 22, 2008 at 2:30 pm

#189586

What's the best way to exclude certain records in a query based on multiple conditions?
Example
Department Category1 Category2 Category3
1 A B C
2 A B C
3 A B C
4 A B C
5 A B C
In this example...each Department is assigned 1 category (1A, 1B, 2A, 4C, etc). I want to include all Departments in the query EXCEPT 2B, 2C, 3A, AND 3C.
Any help would be greatly appreciated!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]

Select {col1, Col2, ...Coln} From {Table}

Where (Department <> 2 And Category2 <> 'B')

And (Department <> 2 And Category3 <> 'C')

And (Department <> 3 And Category1 <> 'A')

And (Department <> 3 And Category3 <> 'C')

confrim on this.

Mahesh

[/font]

MH-09-AM-8694

thehollis Say Hey Kid Points: 698 More actions · Answer 2

Thanks for the reply.

OK...that's what I was thinking, too. However, when I try it in SQL Server Reporting Services, it refuses to view the two conditions together as one condition, no matter how many parentheses I insert. It insists on looking at them as separate criteria.

For example, using your solution, the query would not return any row with Department value of 2 or 3 and also would also not return any row with Category value of A, B, or C.

Is this a bug of Reporting Services or is it operator error?

thehollis Say Hey Kid Points: 698 More actions · Answer 3

I just did more research on the parenthesis problem in SSRS...it's not a bug, but an annoyance of Reporting Services Query Designer.

However, I tried it in the Generic Query Designer (which doesn't reformat the SQL) and the results are the same...it seems to see that first part of the argument (Department <> 2) and doesn't return any row with Department value of 2, instead of combining it with the second condition as well.

I'm thinking this is just the nature of SQL?

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 4

Try

Select {col1, Col2, ...Coln} From {Table}

Where Department+col1 <> '3A'

AND Department+col2 <> '2B'

AND Department+col3 NOT IN ('2C','3C')

See if that makes any difference.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

thehollis Say Hey Kid Points: 698 More actions · Answer 5

Thanks for the reply.

Unfortunately, SSRS didn't like me trying to add a number and text. It did work if I tried with 2 numbers (and used the sum as the <> result) but that opens itself to a big flaw.

What still confounds me is that SSRS will process two conditions as one if:

- 1st condition = AND 2nd condition <>

- 1st condition = AND 2nd condition =

but absolutely REFUSES to accept

- 1st condition <> AND 2nd condition <> as one condition.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

thehollis (4/22/2008)
What's the best way to exclude certain records in a query based on multiple conditions?
Example
Department Category1 Category2 Category3
1 A B C
2 A B C
3 A B C
4 A B C
5 A B C
In this example...each Department is assigned 1 category (1A, 1B, 2A, 4C, etc). I want to include all Departments in the query EXCEPT 2B, 2C, 3A, AND 3C.
Any help would be greatly appreciated!

Hi thehollis

How many category columns do you have? You mention

each Department is assigned 1 category

but there are three category columns in your example, and the other posters have all worked to this. The solutions to the two scenarios (one category columns vs 3 category columns) are likely to be very different.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

thehollis Say Hey Kid Points: 698 More actions · Answer 7

Chris,

Thanks for your reply. The truth is that there are two categories in the real report I'm attempting...I tried to simplify the example. I may have muddled things a bit...sorry.

In the real report, there is a Home Department and a Worked Department (and there are many, many of each). My manager wants to run a report to include all combinations of Home/Worked except certain combinations (about 9-11 of them).

For example...

Home Worked

1000 2000 (include)

1000 2001 (exclude)

1000 1005 (exclude)

2000 3344 (include)

2003 6454 (exclude)

2003 1213 (include)

The only way I can make it work is to add both and make the total a condition of a NOT IN exception (WHERE Home + Worked NOT IN ('3001','2005','8457')...but that would be flawed logic and would very well eliminate other combinations that should be included.

I've posted a more detailed example on another forum...no one has replied there at all. I'm guessing that SQL (at least on SSRS) won't allow a double negative (i.e. <>, NOT IN) to be combined as one condition by its own nature. I'm not sure why that is, but I think that's just the way it is.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Hi

Here's a suggestion, I'm not sure if it will be sympathetic to your environment but worth a try:

SELECT {col1, Col2, ...Coln}

FROM {Table} a

LEFT JOIN (

SELECT 2 AS Department, 'B' AS Category UNION ALL

SELECT 2, 'C' UNION ALL

SELECT 3, 'A' UNION ALL

SELECT 3, 'C'

) d

ON d.Department = a.Department AND d.Category = a.Category

WHERE d.Department IS NULL

Also, rather than adding your filter columns together as numbers which you've realised could blow up, can you cast them as CHAR instead?

Like this:

CAST(Home AS CHAR(4))+CAST(Worked AS CHAR(4)) <> '10002001'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 9

Select {col1, Col2, ...Coln} From {Table}

Where NOT (Department = 2 And Category2 = 'B')

And NOT (Department = 2 And Category3 = 'C')

And NOT (Department = 3 And Category1 = 'A')

And NOT (Department = 3 And Category3 = 'C')

thehollis Say Hey Kid Points: 698 More actions · Answer 10

janine.rawnsley (5/7/2008)
Select {col1, Col2, ...Coln} From {Table}
Where NOT (Department = 2 And Category2 = 'B')
And NOT (Department = 2 And Category3 = 'C')
And NOT (Department = 3 And Category1 = 'A')
And NOT (Department = 3 And Category3 = 'C')

That's the ticket. Thank you!