英文:
List elements that are not in a table without left join
问题
我有一张表格，列出了商店里有的产品。
例如：
| 产品 | 商店 |
|---|---|
| 产品 1 | 1 |
| 产品 1 | 2 |
| 产品 1 | 3 |
另一方面，我有一张列出所有存在的商店的表格。
| 商店 |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
我需要知道哪些产品没有在任何商店里出售。
例如，产品1没有在商店4里出售。
我尝试通过LEFT JOIN 来实现这些情况，但没有成功。
select * from productstore ps
left join stores s on ps.storeid=s.storeid
where prdid='Product 1'
and s.storeid IS NULL
我已经通过以下方式实现了这个目标：
SELECT s.storeid FROM stores s
WHERE not exists
(select ps.storeid from productstore ps
where s.storeid=ps.storeid
and s.PrdId='Product 1')
但这只有在我指定了一个产品时才有效。
什么是获取所有没有在商店出售的产品的最佳方法？
英文:
I have a table where I list the products that are in a store.
For example:
| Product | Store |
|---|---|
| Product 1 | 1 |
| Product 1 | 2 |
| Product 1 | 3 |
On the other hand, I have a table where all the locales that exist are.
| Store |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
I need to know what products are not in any store.
For example, product 1 is not in store 4.
I am trying to reach these cases through LEFT JOIN, but it has not worked for me.
select * from productstore ps
left join stores s on ps.storeid=s.storeid
where prdid='Product 1'
and s.storeid IS NULL
The way that I have achieved it is the following:
SELECT s.storeid FROM stores s
WHERE not exists
(select ps.storeid from productstore ps
where s.storeid=ps.storeid
and s.PrdId='Product 1')
But this works only when I specify a product.
What would be the best way to get all the products that are not in a store?
答案1
得分: 0
似乎您想要获取所有products和stores的交叉连接，其中在productstore中不存在链接。
SELECT
p.productid,
s.storeid
FROM products p
CROSS JOIN stores s
WHERE NOT EXISTS (SELECT 1
FROM productstore ps
WHERE ps.storeid = s.storeid
AND ps.productid = p.productid
);
英文:
It seems you want the cross-join of all products and stores, for which there does not exist a link in productstore.
SELECT
p.productid,
s.storeid
FROM products p
CROSS JOIN stores s
WHERE NOT EXISTS (SELECT 1
FROM productstore ps
WHERE ps.storeid = s.storeid
AND ps.procductid = p.productid
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论