SQL Server List elements that are not in a table without left join [closed]

ecr0jaav 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(118)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed yesterday.
The community is reviewing whether to reopen this question as of yesterday.
Improve this question

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?

sql-server

来源：https://stackoverflow.com/questions/76804471/list-elements-that-are-not-in-a-table-without-left-join

1条答案

按热度按时间

neskvpey1#

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

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server List elements that are not in a table without left join [closed]

1条答案

相关问题

热门标签

最新问答