英文:
Get product and customer name for products not purchased
问题
以下是我的3个表格:
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)
我想要获取未被各自客户购买的产品的产品名称和客户名称。
输出应如下所示:
declare @outputtable table (Cname varchar(10), Pname varchar(10))
insert into @outputtable values('A','P3'),('A','P4'),('B','P2'),('A','P3')
select * from @outputtable
我尝试了左连接,但仍然获取到了被客户购买的记录:
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
英文:
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
答案1
得分: 4
你想要找出产品/客户组合,其中没有购买记录。使用 CROSS JOIN
和 NOT EXISTS
来实现这一点。
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
);
演示:https://dbfiddle.uk/IQk1mVDj
英文:
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
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论