October 23, 2015 at 8:32 am
Hi I need help with this query
I only want the query to return rows that ONLY have ABC, DEF, GHI, but if even one row has XXX or ZZZ then it doesnt return that ID at all.
Here's what the table looks like..
UniqID | Status
1 | ABC
1 | DEF
1 | GHI
1 | XXX
1 | ZZZ
2 | ABC
My code looks like this
Select UniqID
From #table5
WHERE STATUS = 'ABC' Or Status = 'DEF' Or Status = 'GHI
The problem here is that it returns the rows that DO meet this condition I just don't know how to tell it to ignore them if they have any of the XXX or ZZZ in the rows.
Any help is appreciated thanks!
October 23, 2015 at 8:50 am
One method
CREATE TABLE #Test
(
UniqID int,
Status varchar(6)
)
INSERT INTO #Test(UniqID, Status)
VALUES(1, 'ABC'),
(1, 'DEF'),
(1, 'GHI'),
(1, 'XXX'),
(1, 'ZZZ'),
(2, 'ABC')
Select T1.UniqID, T1.STATUS
From #Test T1
WHERE T1.STATUS IN ('ABC', 'DEF', 'GHI')
AND NOT EXISTS(SELECT T2.UniqID
FROM #Test T2
WHERE T2.UniqID = T1.UniqID
AND T2.Status NOT IN ('ABC', 'DEF', 'GHI'))
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 23, 2015 at 9:14 am
Thank you for the reply,
I kind of see what you're doing I'm trying to marry my code to yours and that gets me here, does this look right?
Select UniqID
From #Table5
WHERE Status In ('ABC', 'DEF', 'GHI')
AND NOT EXISTS(SELECT UniqID
FROM #table5
WHERE Status NOT IN ('ABC', 'DEF', 'GHI'))
Sorry im new to this SQL thing
October 23, 2015 at 9:25 am
this article may give you some pointers
http://www.sqlservercentral.com/articles/T-SQL/88244/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply