Search code examples
mysqlsqlselectadventureworks

SQL Server - (AdventureWorks) List the Vendors with no products

I've been given a question using AdventureWorks to list all the vendors with no products. When I run my SELECT statement nothing is returned. What am I doing wrong? (Answer can only be done using Joins and Unions - No Subqueries)

SELECT DISTINCT pv.Name AS 'Vendors'
FROM Purchasing.Vendor pv
INNER JOIN Purchasing.ProductVendor ppv
ON pv.BusinessEntityID = ppv.BusinessEntityID
INNER JOIN Production.Product pp
ON pp.ProductID = ppv.ProductID
WHERE pp.ProductID != ppv.ProductID;
Solution

  • You're looking at one too many tables, all ProductVendors have Products. Not all Vendors have ProductVendors.

    From there you can simply use a LEFT JOIN and look for null records.

    SELECT DISTINCT v.Name
FROM Purchasing.Vendor v
LEFT JOIN Purchasing.ProductVendor pv ON pv.BusinessEntityID = v.BusinessEntityID
WHERE pv.BusinessEntityID IS NULL