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
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
);