CODEWITHSUNDEEP
sqlsql-server
Sleepy_Hollows_4
Sleepy_Hollows_4

Reputation: 9

Using a FROM Clause to Join two Queries into a Sub-Query

I am trying to combine two queries into one uncorrelated expression using the FROM clause. I have attempted to mimic the lesson plan examples from the book, but I keep getting an error regarding my join.

The error is:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'JOIN'.

I assume this means that I am VERY far off on my approach.

The two queries are:

     SELECT Product.product_id, product.product_name
     FROM Product
     JOIN Sells on Sells.product_id = product.product_id
     GROUP BY product.product_id, product.product_name
     HAVING COUNT(sells.store_location_id) = 5;


     SELECT Product.product_name, Sizes.Size_option
     FROM Product
     JOIN Available_in ON Product.product_id = Available_in.product_id
     JOIN Sizes ON  Available_in.sizes_id = Sizes.sizes_id

My Current Code is:

     SELECT Product.product_name, Sizes.Size_option
     FROM (SELECT Sells.product_id
          FROM Sells
          JOIN Sells on Sells.product_id = product.product_id
          GROUP BY Sells.product_id
          HAVING COUNT(*) = 5)
     JOIN Available_in ON Product.product_id = Available_in.product_id 
     JOIN Sizes ON Available_in.Sizes_id = Sizes.sizes_id;

Upvotes: 1

Views: 70

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your current query has multiple errors, because you have sales twice in the subquery.

If I understand correctly, you can write the query as:

 SELECT p.product_name, s.Size_option
 FROM (SELECT s.product_id
       FROM Sells s 
       GROUP BY s.product_id
       HAVING COUNT(*) = 5
      ) sp JOIN
      Available_in a
      ON sp.product_id = a.product_id JOIN
      Sizes s
      ON ai.Sizes_id = s.sizes_id JOIn
      Products p
      ON p.product_id = a.product_id;

Upvotes: 0

kc2018
kc2018

Reputation: 1460

The syntax error may be due to missing alias for your subquery (SELECT sells....). I would approach it by converting the first query into a subquery (products that are sold in 5 locations) and give it the alias Product and join it to the 2nd query:

SELECT Product.product_name, Sizes.Size_option
FROM 
-- first query --
(SELECT Product.product_id, product.product_name
 FROM Product
 JOIN Sells on Sells.product_id = product.product_id
 GROUP BY product.product_id, product.product_name
 HAVING COUNT(sells.store_location_id) = 5) Product

 JOIN Available_in ON Product.product_id = Available_in.product_id
 JOIN Sizes ON  Available_in.sizes_id = Sizes.sizes_id

Upvotes: 0

Aswani Madhavan
Aswani Madhavan

Reputation: 816

"Product" won't be available outside the derived table for joining, Modify code as follows : - 

 SELECT Temp.product_name, Sizes.Size_option
 FROM (SELECT Sells.product_id
      FROM Sells
      JOIN Sells on Sells.product_id = product.product_id
      GROUP BY Sells.product_id
      HAVING COUNT(*) = 5) Temp
 JOIN Available_in ON Temp.product_id = Available_in.product_id 
 JOIN Sizes ON Available_in.Sizes_id = Sizes.sizes_id;

Upvotes: 2

Related Questions