Get product and customer name for products not purchased

July 27, 2023

Below, are my 3 tables:-

declare @customer table(CId int identity,Cname varchar(10))
    insert into @customer values('A'),('B')
    --select * from @customer
    declare @Product table(PId int identity,Pname varchar(10))
    insert into @Product values('P1'),('P2'),('P3'),('P4')
    --select * from @Product
    declare @CustomerProduct table(CPId int identity,Cid int, Pid int)
    insert into @CustomerProduct values(1,1),(1,2),(2,1),(2,4)

I want to get the product name and customer name for the product not purchased by respective customers.

Output should be like below:-

declare @outputtable table (Cname varchar(10), Pname varchar(10))
insert into @outputtable values('A','P3'),('A','P4'),('B','P2'),('A','P3')
select * from @outputtable

I tried with left join but still I am getting records purchased by the customer:-

Select P.Pname,C.Cname from @Product p
left join @CustomerProduct cp on cp.Pid=p.pId
Left Join @customer c  on cp.Cid=c.CId
where c.CId=cp.Cid

>Solution :

You want product/customer combinations for which not exists a purchase. Use a CROSS JOIN and NOT EXIST for this.

select *
from @customer c
cross join @product p
where not exists
(
  select null
  from @customerproduct cp
  where cp.cid = c.cid and cp.pid = p.pid
);

Demo: https://dbfiddle.uk/IQk1mVDj