Weston Sapusek Weston Sapusek - 3 years ago 98
SQL Question

SQL search for suppliers that provide exactly 2 different products

This is a homework question using subqueries, and the closest example the professor has given wasn't that helpful. We basically have a big table of products with the relevant rows

ProductID, SupplierID, CategoryID
I need to write query that returns all suppliers that provide exactly 2 categories of items.

I have something like this

select Products.SupplierID, CompanyName, Products.CategoryID, CategoryName
from Products
join Categories ON Products.CategoryID = Categories.CategoryID
join SUPPLIERS ON Products.SupplierID = Suppliers.SupplierID
where Products.SupplierID = ANY (select top 3 with ties Products.SupplierID
from Products
group by Products.SupplierID
order by count(*) desc);

Just working with examples, the where statement isn't right and I honestly have no idea where to start here. I'm not asking anyone to solve this for me but if you could point me in the right direction I would appreciate it.

Answer Source

I think that you would just need to change your WHERE Statement to look for the Supplier ID's that have 2 distinct CategoryID's. You are really close, it would just be removing the Top 3 and Order by, then adding a HAVING statement at the end showing COUNT(DISTINCT(Products.CatigoryID)) = 2.

I hope that helps get you where you need to go.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download