CODEWITHSUNDEEP
sqlt-sql
Pseudonym
Pseudonym

Reputation: 2072

Select No Rows If Any Row Meets A Condition?

How can I select no rows if any row in the result set meets a certain condition?

For instance:

Id|SomeColumn|Indicator
1 | test     | Y
1 | test1    | Y
1 | test2    | X
2 | test1    | Y
2 | test2    | Y
3 | test1    | Y

Say I wanted to select all rows where Id = 1 unless there is a row with an indicator = X

Currently I am doing something like this

SELECT * FROM SOMETABLE WHERE ID = 1 AND INDICATOR = 'Y' AND ID NOT IN (SELECT ID WHERE INDICATOR = 'X')

But that feels really clunky and I feel like there could be a better way to be doing this. Is there or am I just being overly sensitive

Upvotes: 0

Views: 711

Answers (3)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Another option, assuming that there's an enforced order in indicator column.

DECLARE @T TABLE
    (
      ID INT
    , someColumn VARCHAR(5)
    , Indicator CHAR(1)
    )
INSERT  INTO @T
        ( ID, someColumn, Indicator )
VALUES  ( 1, 'test', 'Y' ),
        ( 1, 'test1', 'Y' ),
        ( 1, 'test2', 'X' ),
        ( 2, 'test1', 'Y' ),
        ( 2, 'test2', 'Y' ),
        ( 3, 'test1', 'Y' )

SELECT t.ID
       , t.someColumn
       , t.Indicator
FROM @T t
    JOIN (SELECT ID
            FROM @T t2
            GROUP BY t2.ID
            HAVING MIN(indicator) >= 'Y') q ON q.ID = t.ID

Not sure if it's any less clunky, but it may perform better since it's using positive exclusion rather than negative.

Upvotes: 1

xlecoustillier
xlecoustillier

Reputation: 16351

Something like this ?

SELECT * 
FROM SOMETABLE 
WHERE ID = 1 
AND NOT EXISTS (SELECT 1 FROM SOMETABLE WHERE INDICATOR = 'X')

or, if you want the X to discriminate only on the same id:

SELECT * 
FROM SOMETABLE t1
WHERE t1.ID = 1 
AND NOT EXISTS (SELECT 1 FROM SOMETABLE t2 WHERE t1.ID = t1.ID AND INDICATOR = 'X')

Upvotes: 2

Jeffrey Wieder
Jeffrey Wieder

Reputation: 2376

There are not too many options to do this. Another option is to use EXISTS.

SELECT * 
FROM SOMETABLE s1 
WHERE ID = 1 AND INDICATOR = 'Y' 
     AND NOT EXISTS (SELECT TOP 1 ID FROM SOMETABLE s2 WHERE s1.ID = s2.ID AND INDICATOR = 'X')

Upvotes: 1

Related Questions