Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL ignoring multiple AND clauses Expand / Collapse
Author
Message
Posted Thursday, February 27, 2014 7:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 8:21 AM
Points: 1, Visits: 15
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?

Post #1545870
Posted Thursday, February 27, 2014 10:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 4:09 AM
Points: 37, Visits: 142
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')
Post #1546195
Posted Friday, February 28, 2014 6:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 135, Visits: 2,051
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'))
Post #1546285
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse