CODEWITHSUNDEEP
sqlsql-server
user3427540
user3427540

Reputation: 1172

How to select rows from a group using a condition

I have a table which contains data in this format.

ProductID   ShipId
11          1
11          2
11          3
22          1   
22          2
33          1
33          2

Now I want only the distinct product ids where ship id 3 is not associated. Output should be

22,33 only.

I have used this query but it throws error.

Select distinct productid from X_product_ship group by productid having shipid <> 3

Please help.

Upvotes: 2

Views: 47

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Use a subquery in the where clause to exclude the products that has a shipid of three.

select distinct P1.ProductID
from dbo.X_product_ship as P1
where P1.ProductID not in (
                          select P2.ProductID
                          from dbo.X_product_ship as P2
                          where P2.ShipId = 3
                          )

SQL Fiddle

Or you could get creative in the having clause using a case statement.

select P.ProductID
from dbo.X_product_ship as P
group by P.ProductID
having max(case when P.ShipId = 3 then 1 else 0 end) = 0

SQL Fiddle

Upvotes: 4

Related Questions