CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
user1083828
user1083828

Reputation: 43

Find data that exists for one record but not another record

I have two tables. #tempinfo contains an order number and client name. #tempproduct contains the order number, client name, and products ordered.

I need to find products that exist in one order for a client, but missing from the other order for the client.

create table #tempinfo (OrderNumber int, Client varchar(25))
create table #tempproduct (productid int, OrderNumber int, Client varchar(25), Products varchar(50)

insert into #tempinfo values (1, 'Albertsons')
insert into #tempinfo values (2, 'Albertsons')
Insert into #tempinfo values (3, 'Krogers')
Insert into #tempinfo values (4, 'Krogers')
Insert into #tempinfo values (5, 'Krogers')
Insert into #tempinfo values (6, 'Smiths')
Insert into #tempinfo values (7, 'Smiths')

insert into #tempproduct (1, 1, 'Albertsons', 'Oranges')
insert into #tempproduct (2, 1, 'Albertsons', 'Apples')
insert into #tempproduct (3, 2, 'Albertsons', 'Oranges')
insert into #tempproduct (4, 2, 'Albertsons', 'Apples')
insert into #tempproduct (5, 2, 'Albertsons', 'Grapes')
insert into #tempproduct (6, 3, 'Krogers', 'Pencils')
insert into #tempproduct (7, 3, 'Krogers', 'Pens')
insert into #tempproduct (8, 3, 'Krogers', 'Staples')
insert into #tempproduct (9, 5, 'Krogers', 'Pencils')
insert into #tempproduct (10, 5, 'Krogers', 'Erasers')
insert into #tempproduct (11, 6, 'Smiths', 'Soda')
insert into #tempproduct (12, 6, 'Smiths', 'Buns')
insert into #tempproduct (13, 6, 'Smiths', 'Ice')
insert into #tempproduct (14, 7, 'Smiths', 'Buns')

I would like to see: Albertsons order 1 is missing 'Grapes' Krogers order 3 is missing 'Erasers' Krogers order 5 is missing 'Pens' and 'Staples' Smiths order 7 is missing 'Soda' and 'Ice'

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is an interesting problem. The idea is to generate all rows for a client -- that is, all products for the client for each order.

Then use a left join to find the ones that match, and filter these out:

select cp.client, cp.product, co.ordernumber
from (select distinct client, product from tempproduct) cp  join
     (select distinct client, ordernumber from tempproduct) co
     on cp.client = co.client left join
     tempproduct tp
     on tp.client = cp.client and
        tp.product = cp.product and
        tp.ordernumber = co.ordernumber
where tp.client is null;

Here is a db<>fiddle.

Upvotes: 1

Related Questions