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