sql - SQL Selecting ONLY products that are available in every location
问题描述
I'm trying to combine these queries with subqueries to select products that exist in every location in the store_location table. There are multiple tables in this schema but the most relevant ones are Product, Sells, Sizes, and Store_Location. Sells is the table that holds both product_id and store_location_id. Sells can therefore be joined to store_location (in a subquery?)
I've learned how to do aggregates, and it seems like I need a count of the number of locations, but how can I use a comparison in the where clause to filter out products that aren't available everywhere? Hopefully you can see enough information about the schema and what I'm attempting to do with the code below:
Edit: Thank you to the person who tagged this with relational division, you may have given me the clue that I needed!
select distinct product_name, size_option
from product p
join available_in a on p.product_id = a.product_id
join sells s on p.product_id = s.product_id
join sizes si on si.sizes_id = a.sizes_id
order by size_option desc;
select product_id
from sells s
join store_location st on st.store_location_id = s.store_location_id
where ???
select count(store_location_id)
from store_location
解决方案
So I read up on relational division and was able to come up with the answer:
select distinct product_name, size_option
from product p
join available_in a on p.product_id = a.product_id
join sells s on p.product_id = s.product_id
join sizes si on si.sizes_id = a.sizes_id
join store_location st on st.store_location_id = s.store_location_id
where s.product_id in (select x.product_id
from sells x)
group by product_name, size_option
having count(*) = (select count(*) from store_location)
order by size_option desc;
It works! I appreciate the clue :)
推荐阅读
- class-validator - 类验证器:验证子类型:拒绝无效的鉴别器值,避免查看数组内部
- java - 如何在 Java 中反转 InputStream?
- ruby - Ruby 3.0 中的 define_method 问题
- cordova - 升级到 Cordova iOS 6 并更改配置文件,从我的应用程序中注销用户
- python - 在python中对锯齿状数组中的项求和
- c# - 尽管安装了包,但无法找到 NuGet 包 NU1101
- serilog - Serilog 不在生产服务器上创建日志文件
- python - Python/SQL/pydobc:获取计数器的 if 操作结果?
- c - 为什么将 &str 转换为 *const i8,将其发送到 C 或从 C 发送,然后再转换回 &str 会导致 Utf8Error?
- python - python图中的3d效果