获取未购买产品的产品和客户名称。

huangapple go评论95阅读模式
英文:

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

Demo: https://dbfiddle.uk/IQk1mVDj

huangapple
  • 本文由 发表于 2023年7月27日 19:05:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/76779119.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定