Sql-server – Complex joins in multiple subqueries

countjoin;sql serversubquerywhere

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.

Best Answer

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

Related Question