Complex joins in multiple subqueries

Question

Sorry to keep bugging you guys, but SQL is still new to me. I have these tables: Store_location, Product, Sizes, Sells, Available_in, Offers, and Currency. The goal is to be able to run one query with multiple subqueries (a WHERE clause is required) that will return only products that are sold in ALL store locations, and nothing else. It also has to be scalable so that if any stores open or close, the code does not need to be altered. I have these ramblings to get me started, but I do not know where to go from there: The first select statement below is what needs to be displayed when the query succeeds. SELECT Store_location.store_name,Product.product_name,Sizes.size_option, COUNT(store_location.store_id) AS store_count

JOIN Sells ON Sells.store_location_id = Store_location.store_location_id
JOIN Product ON Product.product_id = Sells.product_id
JOIN ON Available_in.product_id = Product.product_id
JOIN ON Available_in.sizes_id = Sizes.sizes_id

I tried to get the joins done to show where the tables I need to use have foreign key constraints. IF you need any additional information, I can provide it. I added a link showing the contents of all the involved tables. I know I need to embed at least one subquery in a WHERE statement, but not sure what to put in there. I know it's a lot of information to go over, and I understand if nobody has the time to help, but any guidance at all would be appreciated.

I realize it's a late request, but if anyone can help me with a way to also use EXISTS on this I'd be very appreciative.

• S3S: what is the sells table? Does this show items sold or just product availability by store location? It's hard to see how you determine availability in the link you provided
• user121168: The Sells and Available_in tables are basically bridge tables. Available_in allows the product_id to be tied to the size of the product, and Sells links the product_id to the store_id that sells it. So effectively I need to pull what products each store sells based on the Sells table, and pull what the sizes are over from the available_in/Sizes table.
• S3S: Ok so you just want to see if the store sells it, not if it is "in stock". I misunderstood the word "available" in your question.
• user121168: Yeah, sorry about that, I could have been more clear. I don't think anything in any of the tables indicates "in stock", just that it is sold there or not.

Answer 1

Seems like the most difficult part if finding out which products are sold in all stores. As with most things, there's more than one way to do this, but the following query should return all product IDs that are sold in all stores:

SELECT product_id
 FROM Product
EXCEPT -- exclude product IDs that are not sold in all stores
SELECT product_id
 FROM (-- Generate list of product IDs and the stores that do NOT sell them
 -- select all possible products and stores ...
 SELECT product_id, store_id
 FROM Product CROSS JOIN Store_location
 EXCEPT -- ... for the products actually sold by each store
 SELECT product_id, store_id
 FROM Sells
 ) Not_At_All_Stores

The sub-query takes the list of all possible products and stores, and removes the combos that represent products actually sold in the stores in question. This just leaves products that are not sold at the stores indicated.

Then, we take the full list of products, and remove any product that we (now) know are not sold in at least one store. That just leaves the products sold at all stores.

From here, it's just a matter of joining everything together. You'll wind up with a FROM clause like this:

SELECT Store_location.store_name
 ,Product.product_name
 ,Sizes.size_option
 FROM (
 SELECT product_id
 FROM Product
 EXCEPT -- exclude product IDs that are not sold in all stores
 SELECT product_id
 FROM (-- Generate list of product IDs and the stores that do NOT sell them
 -- select all possible products and stores ...
 SELECT product_id, store_id
 FROM Product CROSS JOIN Store_location
 EXCEPT -- ... for the products actually sold by each store
 SELECT product_id, store_id
 FROM Sells
 ) Not_At_All_Stores
 ) uprod -- for a Universally sold PRODuct
 INNER JOIN Product ON (uprod.product_id = Product.product_id)
 INNER JOIN Available_in ON (uprod.product_id = Available_in.product_id
 INNER JOIN Sizes ON (Available_in.size_id = Sizes.size_id)
 INNER JOIN Sells ON (uprod.product_id = Sells.product_id)
 INNER JOIN Store_location ON (Sells.store_id = Store_location.store_id)
 ORDER BY store_name, product_name, size_option

No WHERE clause is necessary (based on what you asked for). Note that the store information is redundant - since these products are sold at all stores, you're just getting 5 copies of the same underlying result, with one of the 5 store names prepended to each copy. The store name is much more interesting for products that aren't sold at every store.

NOTE: Depending on how your Product table is indexed, you could add the product_name column into the Not_At_All_Stores subquery, and carry it through from there. However, this does mean that we'd have to include it in our list of all possible products and stores, which would consume more memory while doing our EXCEPT operations.

UPDATE: From your latest comments, apparently this assignment requires that you use a WHERE clause, a subquery in the WHERE clause, and EXISTS. To get there from here:

• Drop uprod from the query; anything linked to uprod.product_id should link to Product.product_id instead;
• Use the very first query in may answer as your sub-query;
• You want the records where the product_id exists in the subquery.

Since this is a homework assignment, I'll let you actually assemble it, at least.

use the very first query in my answer as your sub_query, and look for product

• user121168: Both of you got the same result with your queries, so this is definitely on the right track! Now I just need to get it to show the store_name, product_name, and size_option for each product that is at all stores. I tried adding them to the SELECT but, naturally, they can't be bound. Is there a simple way? I know there are a lot of tables involved in getting this the way I need it.
• user121168: Very well done @RDFozz, but it is far more advanced than the material I am using to design it! I am not sure they'll believe I even wrote this code, but I guess I can alter it a tad to better suit the coursework!
• user121168: any chance of a variation that does use WHERE? I didn't realize it was required for part 1.
• RDFozz: Updated with basic directions to convert it so it uses WHERE, EXISTS, and a subquery.

Answer 2

You can solve this using a HAVING clause.

with cte as(
Select
 p.product_id
 ,p.product_name
from
 Product p
 inner join
 sells s on
 s.product_id = p.product_id
group by
 p.product_id
 ,p.product_name
having count(p.product_id) = (select count(distinct store_location_id) from sells))
select
 c.product_id
 ,c.product_name
 ,s.store_location_id
 ,a.sizes_id
from
 cte c
 inner join
 sells s on
 s.product_id = c.product_id
 inner join
 Avaliable_in a on
 a.product_id = c.product_id

• user121168: Can I just throw in the location and size in the first SELECT? It needs to display where it is sold and what sizes as well.
• S3S: user @user121168 i edited the answer
• S3S: Just to note... this should be quite faster than any query which has a lot of or nested sub-queries. Be sure to check the execution plans.
• S3S: @user121168 tell your professor to stop teaching poor ways to accomplish simple tasks :)
• S3S: In the future, list all of these requirements up front and let everyone know it's homework. It will save a lot of time.