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

Where condition not equal to multiple columns - PUZZLE - who can solve this? Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 3:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:05 AM
Points: 5, Visits: 115
Concerning the puzzle below, need a SQL guru to tell me how they solved this problem.
Goal:
To get statement 2 to produce the same results as statement 1 without having to string together the columns.

DECLARE @Table1 TABLE
(
Column1 int IDENTITY(1,1),
Column2 varchar(12),
Column3 varchar(12)
)

INSERT INTO @Table1 (Column2, Column3) VALUES ('UK','U')
INSERT INTO @Table1 (Column2, Column3) VALUES ('AP','U')
INSERT INTO @Table1 (Column2, Column3) VALUES ('AP','O')
INSERT INTO @Table1 (Column2, Column3) VALUES ('UK','O')

--Statement 1
select * from @Table1
where (column2 + column3 != 'APU')

--Statement 2
select * from @Table1
where (column2 != 'AP' and column3 != 'U')
Post #1408641
Posted Thursday, January 17, 2013 3:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:23 AM
Points: 5,074, Visits: 11,852
--Statement 2
select * from @Table1
where (column2 != 'AP' or column3 != 'U')




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1408643
Posted Thursday, January 17, 2013 4:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 6,256, Visits: 7,438
EDIT: Scratch this, bad code. Sorry about that.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1408658
Posted Thursday, January 17, 2013 4:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 6,256, Visits: 7,438
*facepalm* sorry about the earlier code. Try this again:

Alternatively:

SELECT
*,
CASE WHEN column2 <> 'AP' THEN 1 ELSE 0 END AS APtest,
CASE WHEN column3 <> 'U' THEN 1 ELSE 0 END AS Utest,
CASE WHEN column2 <> 'AP' AND column3 <> 'U' THEN 1 ELSE 0 END AS APUtest
FROM
@Table1
WHERE
NOT ( column2 = 'AP' AND column3 = 'U')




- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1408661
Posted Thursday, January 17, 2013 4:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229
Evil Kraig F (1/17/2013)
*facepalm* sorry about the earlier code. Try this again:

Alternatively:

SELECT
*,
CASE WHEN column2 <> 'AP' THEN 1 ELSE 0 END AS APtest,
CASE WHEN column3 <> 'U' THEN 1 ELSE 0 END AS Utest,
CASE WHEN column2 <> 'AP' AND column3 <> 'U' THEN 1 ELSE 0 END AS APUtest
FROM
@Table1
WHERE
NOT ( column2 = 'AP' AND column3 = 'U')



Phil Parkin (1/17/2013)


--Statement 2
select * from @Table1
where (column2 != 'AP' or column3 != 'U')




For those not versed in boolean logic:

NOT ( column2 = 'AP' AND column3 = 'U') -->
NOT(column2 = 'AP') OR NOT(column3 = 'U') -->
column2 != 'AP' or column3 != 'U'

Which means: NOT ( column2 = 'AP' AND column3 = 'U') is equivalent to: column2 != 'AP' or column3 != 'U'.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1408674
Posted Tuesday, January 22, 2013 10:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:05 AM
Points: 5, Visits: 115
Phil,
Thanks for the help. I had tried the or logic, but something else must have affected the results.
Post #1410141
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse