CODEWITHSUNDEEP
sqlsql-server
LCJ
LCJ

Reputation: 22662

Group By and HAVING along with Distinct

I have ItemDetails table that has item and vendor information. There are duplicate records for item-vendor association – that is okay in my scenario.

Now, I need to find out items for which more than one distinct vendors exists. What are the SQL queries for this? I am looking for multiple approaches for this.

The following query is not correct. It is listing both ‘A1’ and ‘A2’. The correct query should return ‘A2’ only.

SELECT Item FROM @ItemDetails 
GROUP BY Item
HAVING COUNT(*) > 1

TABLE 

DECLARE @ItemDetails TABLE (MyPrimaryKey INT, Item VARCHAR(5), VendorID VARCHAR(5))
INSERT INTO @ItemDetails VALUES (1, 'A1', 'V1')
INSERT INTO @ItemDetails VALUES (2, 'A1', 'V1')
INSERT INTO @ItemDetails VALUES (2, 'A2', 'V1')
INSERT INTO @ItemDetails VALUES (2, 'A2', 'V2')

Upvotes: 0

Views: 83

Answers (1)

AnatolyS
AnatolyS

Reputation: 4319

The idea is to gather items which produced by single vendor and after that filter out other

select a.item 
from ItemDetails as a 
where a.item not in (
   select b.item 
   from ItemDetails as b 
   group by b.Item, b.VendorId
   having count(*) = 1
)

but after one minute I found the easiest way

select item 
from ItemDetails
group by Item
having count(distinct VendorId) > 1

Upvotes: 1

Related Questions