CODEWITHSUNDEEP
mysqlsqlselectadventureworks
Dane Cameron
Dane Cameron

Reputation: 433

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;

Upvotes: 2

Views: 1501

Answers (3)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28854

  • Use left join to include cases where there is no product for a "vendor". Inner join will consider only those cases, where a product id exists for a vendor.
  • Now, do grouping on a "Vendor" and count the number of products using COUNT() function.
  • Finally, filter out those vendors where count is zero, using HAVING clause

Try the following: 

SELECT pv.Name AS 'Vendors', 
       Count(pp.ProductID) AS count_products 
FROM Purchasing.Vendor pv
LEFT JOIN Purchasing.ProductVendor ppv
ON pv.BusinessEntityID = ppv.BusinessEntityID
LEFT JOIN Production.Product pp
ON pp.ProductID = ppv.ProductID 
GROUP BY pv.Name 
HAVING count_products = 0;

Upvotes: 1

Derrick Moeller
Derrick Moeller

Reputation: 4960

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

Upvotes: 3

dMazay
dMazay

Reputation: 65

SELECT DISTINCT pv.Name AS 'Vendors'
  FROM Purchasing.Vendor pv
  INNER JOIN Purchasing.ProductVendor ppv
     ON pv.BusinessEntityID = ppv.BusinessEntityID
  where not exists (SELECT 1 Production.Product pp  Where pp.ProductID = ppv.ProductID)

Return all vendors wich donot have any product

Upvotes: -1

Related Questions