CODEWITHSUNDEEP
sql-servert-sql
Tom T
Tom T

Reputation: 273

T-SQL find missing items between two tables

I have a Sales table and a Product table. Sample:

Company    Product            Product
Company1   A                  A
Company1   B                  B
Company2   B                  C
                              D

Company1 bought products A and B. Company2 bought product B.

I need a T-SQL query that will tell me, by company, which products they did not buy. The end user would like to approach these companies to see if they are interested in purchasing the "missing" products, so we need to know which products to discuss with each customer:

I want to generate a report for all the companies, so it's not sufficient to run a query and supply a single company ID. We need to group the results by company:

Company1: C, D   (I don't need comma-delimited list; I'm just listing the values as an example)
Company2: A, C, D

Upvotes: 0

Views: 90

Answers (1)

Stu
Stu

Reputation: 32614

Something as simple as the following will get you the products in question:

select distinct s.company, p.product
from sales s
outer apply (
    select product
    from products
    where product not in (select product from sales s2 where s2.company=s.company)
)p

Upvotes: 1

Related Questions