CODEWITHSUNDEEP
sqlsql-server-2008
suji
suji

Reputation: 53

SQL server 2008 , conditional select query

Please help me to form this query.

I have a table like this:

Row Product   Role            
-------------------
1   ABC-1     Self
2   ABC-2     Self
3   ABC-2     Joint
4   ABC-3     Self
5   ABC-3     Joint
6   ABC-4     Self

I want to fetch only those product rows where Role is Self only. In other words from the given table the SQL query should output only rows 1 & 6. Output should look like this

Row Product   Role            
-------------------
1   ABC-1     Self
6   ABC-4     Self

Thank you

Upvotes: 0

Views: 52

Answers (2)

Lamak
Lamak

Reputation: 70638

You can use NOT EXISTS:

SELECT *
FROM dbo.YourTable t
WHERE NOT EXISTS(SELECT 1 FROM dbo.YourTable
                 WHERE Product = t.Product
                 AND Role <> 'Self');

Upvotes: 1

Siyual
Siyual

Reputation: 16917

You can use a WHERE NOT EXISTS for this:

Select  *
From    YourTable   T
Where Not Exists
(
    Select  *
    From    YourTable   T1
    Where   T.Product = T1.Product
    And     T1.[Role] <> 'Self'
)
And    T.[Role] = 'Self'

Upvotes: 1

Related Questions