February 27, 2014 at 7:13 am
I am running a query that where an input parameter is set to "N" it should ignore rows where column a is "x" and column b is "-99"
The query I am trying is:
DECLARE @IncludeUnavailableBookings VARCHAR(1)
SET @IncludeUnavailableBookings = 'N'
SELECT
*
FROM
Table1 A
LEFT JOIN Table2 B ON B.ID = A.ID
WHERE
(
(@IncludeUnavailableBookings = 'Y') OR
(@IncludeUnavailableBookings = 'N' AND (A.Col1 <> 'X' AND A.Col2<> '-99'))
)
However this excludes all rows where EITHER Col1 = 'X' OR A.Col2= '-99'
What I want to do is only exclude rows where both conditions are met. I can get around this by concatenating the two columns as follows:
WHERE
(
(@IncludeUnavailableBookings = 'Y') OR
(@IncludeUnavailableBookings = 'N' AND A.Col1+CONVERT(VARCHAR(20),A.Col2) <> 'X-99'))
However can anyone tell me why the first query is not working the same way please?
February 27, 2014 at 10:40 pm
if you put OR in place of AND (Col1 <> 'X' or Col2<> '-99'), you will get same result from both the Sql
create table #temp (id int,col1 varchar(30),col2 varchar(30))
insert into #temp(id,col1,col2)
select 1,'A','-99'
union all
select 2,'B','10'
union all
select 3,'X','-99'
union all
select 4,'Z','-99'
union all
select 5,'X','-99'
DECLARE @IncludeUnavailableBookings VARCHAR(1)
SET @IncludeUnavailableBookings = 'N'
select * from #temp where @IncludeUnavailableBookings = 'N' AND (Col1 <> 'X' or Col2<> '-99')
select * from #temp where @IncludeUnavailableBookings = 'N' AND (Col1+CONVERT(VARCHAR(20),Col2) <> 'X-99')
February 28, 2014 at 6:10 am
negative logic: ( a & b) <=> (!a | !b)
you want to select
WHERE a OR (b AND NOT (c AND d))
which would be equiv. to
WHERE a OR (b AND (NOT c OR NOT d))
so either:
(@IncludeUnavailableBookings = 'Y') OR
(@IncludeUnavailableBookings = 'N' AND NOT (A.Col1 = 'X' AND A.Col2 = '-99'))
or:
(@IncludeUnavailableBookings = 'Y') OR
(@IncludeUnavailableBookings = 'N' AND (A.Col1 <> 'X' OR A.Col2 <> '-99'))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply