rows without other occurence of a value

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)

rows without other occurence of a value

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-02-02 : 06:01:55

Hi, I need to find all rows which have a particular value in a column and no other rows with a different value.
Here is a test table:
CREATE TABLE [dbo].[ATestTable](
[Col] [nchar](10) NULL,
[ID] [int] NULL
) ON [PRIMARY]

INSERT INTO [ATestTable]
(Col,ID)

Select 'a',15
UNION ALL
Select 'b',15
UNION ALL
Select 'c',22
UNION ALL
Select 'a',12
UNION ALL
Select 'b',27
UNION ALL
Select 'c',30
UNION ALL
Select 'a',144
UNION ALL
Select 'b',15
UNION ALL
Select 'c',12
UNION ALL
Select 'd',15
UNION ALL
Select 'e',15
UNION ALL
Select 'e',15
UNION ALL
Select 'e',15
UNION ALL
Select 'e',15

In the sample above we are looking the [Col] with ID=15 and only ID=15.
So a,b,c,d,e =15
and a,b,c <>15
but only d and e only =15.
But how to find it? I tried this but it is no where near right:

Select WithIt.Col ,WithIt.ID from (SELECT [ID]
,[Col]
FROM [ATestTable]
where ID<>15 ) as Without
join
(SELECT [ID]
,[Col]
FROM [ATestTable]
where ID=15 ) as WithIt
on Without.Col=WithIt.Col and WithIt.ID=15

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-02 : 06:14:18

There may be better ways, but the obvious way is:


SELECT Col
FROM ATestTable
GROUP BY Col
HAVING COUNT(ID) = SUM(CASE WHEN ID = 15 THEN 1 ELSE 0 END)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-02 : 06:23:28

You could also try:


SELECT Col
FROM ATestTable
GROUP BY Col
HAVING SUM(1 - CASE WHEN ID = 15 THEN 1 ELSE 0 END) = 0

lappin
Posting Yak Master

182 Posts

Posted - 2011-02-02 : 06:43:58

Both methods worked great thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources