"All or none" exclusions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

"All or none" exclusions

Author

Topic

ATG
Starting Member

35 Posts

Posted - 2011-10-03 : 17:47:01

Lets say I have a table with several entries. A name column and a car column. I want to be able to Select all people who own a car that is not a Toyota.

NAME CAR
Bob Toyota
Bob Ford
Ron Chevy
Ben Kia

I would want it to return

Ron Chevy
Ben Kia

Because Bob owns a Toyota even though he has a Ford too. How could I exclude all his records based on the one that contains "Toyota"?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-03 : 17:57:05

SELECT NAME, CAR FROM myTable A
WHERE NOT EXISTS(SELECT * FROM myTable WHERE NAME=A.NAME AND CAR='Toyota')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 00:37:39

[code]
SELECT Name,Car
FROM
(
SELECT Name,CAR,SUM(case when CAR='Toyota' THEN 1 ELSE 0 END) OVER(PARTITION BY Name) AS Excl
FROM table
)t
WHERE Excl=0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m_imran18
Starting Member

14 Posts

Posted - 2011-10-04 : 02:25:37

Drop Table test
go
Create table test
([Name] nvarchar(50)
,[Car] nvarchar(50))
GO

insert into test Select 'Bob' ,'Toyota'
Union
Select 'Bob', 'Ford'
Union
Select 'Ron', 'Chevy'
Union
Select 'Ben','Kia'

GO
Select * from test Where [name] Not In (
Select [name] from test Where [Car]='toyota')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources