CODEWITHSUNDEEP
sqlsql-serversql-server-2005selectwhere-clause
thevan
thevan

Reputation: 10354

Select a Column based on Condition?

I have a Detail table "Detail" which is like below:

Detail:

    ID          StatusID
   ----         --------
     1             4
     1             4
     2             4
     2             3
     3             4   
     3             4
     4             3
     4             3

Here, I want to select only the ID which has all the StatusID = 4:

My Desired Result should be like below:

  ID
 ----
  1
  3

How to achieve this?

Upvotes: 2

Views: 561

Answers (3)

sorpigal
sorpigal

Reputation: 26086

Just for fun, how about a join version

select distinct
        t.id
    from
        your_table as t
        left outer join(select
                id,
                statusid
            from
                your_table
            where
                statusid != 4
        ) as j
            on t.id = j.id
    where
        j.id is null
;

Upvotes: 1

Christian Specht
Christian Specht

Reputation: 36431

select distinct ID 
from YourTable
where ID not in
(
    select ID 
    from YourTable
    where StatusID <> 4
)

Upvotes: 1

Andomar
Andomar

Reputation: 238086

You could use a not exists subquery:

select  distinct yt1.ID
from    YourTable yt1
where   yt1.StatusID = 4
        and not exists
        (
        select  *
        from    YourTable yt2
        where   yt2.StatusID <> 4
                and yt2.ID = yt1.ID
        )

Upvotes: 2

Related Questions