CODEWITHSUNDEEP
sql
user1278496
user1278496

Reputation: 1066

SQL Query for records with ONLY one condition

I have a table that features products with different media IDs. Most products have a number of media ID's like so:

ISISProductCode ISISMediaId
010015.000.000          9
010015.000.000          16
010015.000.000          10
010015.000.000          11
010015.000.000          12

How can I find products with ONLY 1 Media ID. I tried the query below but all that is bringing back is products that HAVE a Media ID of 10, but doesn't bring back products with ONLY a Media ID of 10.

select *
from dbo.Product
join dbo.Item
on dbo.Item.ISISStyleId = dbo.Product.ISISStyleId
where ISISMediaId = '10'
order by ISISProductCode

Upvotes: 0

Views: 28

Answers (1)

juergen d
juergen d

Reputation: 204746

Group by the ISISProductCode and count all the ISISMediaId that are not 10 in a having clause.

select ISISProductCode 
from dbo.Product
join dbo.Item on dbo.Item.ISISStyleId = dbo.Product.ISISStyleId
group by ISISProductCode 
having sum(case when ISISMediaId <> '10' then 1 else 0 end) = 0
order by ISISProductCode

Upvotes: 3

Related Questions