Where condition not equal to multiple columns - PUZZLE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Where condition not equal to multiple columns...

Where condition not equal to multiple columns - PUZZLE - who can solve this?

Rate Topic

Display Mode

Topic Options

Author

Message

Mach1000

Posted Thursday, January 17, 2013 3:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 07, 2013 9:59 AM
Points: 5, Visits: 104

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

Phil Parkin

Posted Thursday, January 17, 2013 3:30 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 4,240, Visits: 9,487

--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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1408643

Evil Kraig F

Posted Thursday, January 17, 2013 4:10 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:44 PM
Points: 5,677, Visits: 6,124

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

Evil Kraig F

Posted Thursday, January 17, 2013 4:14 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:44 PM
Points: 5,677, Visits: 6,124

*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')

Post #1408661

Lynn Pettis

Posted Thursday, January 17, 2013 4:58 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 21,615, Visits: 27,447

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'.

Cool

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

Mach1000

Posted Tuesday, January 22, 2013 10:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 07, 2013 9:59 AM
Points: 5, Visits: 104

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 »

Permissions