Mike Mike - 6 months ago 14
SQL Question

SQL nested Inner Joins? Or Where Clause On Inner Joins?

I am having an issue with a query I am trying to write. Unfortunately I can only query against the database and cannot change the schema. Here are some simplified example tables:

Table 1
itemID sale date
----------------------------
1 1/2015
1 3/2016
2 5/2016
2 1/2015

Table 2
itemID colorID price
--------------------------------------
1 1 23
1 2 10
1 3 13
2 1 11
2 2 14
2 3 18

Table 3
ColorID color
---------------------------------------
1 Red
2 Blue
3 Green

Table 4
SaleBegin SaleEnd ColorID
----------------------------------------
1/1/2014 12/31/2014 1
1/1/2015 12/31/2015 2
1/1/2016 12/31/2016 3


Now I need a query that essentially gets the price and color for both item ids in the first table. I'm not sure how to do it with this schema even just for one. I tried something like:

SELECT item_id, price, color FROM Table1 T1

INNER JOIN Table2 T2
ON T1.ItemID=T2.ItemID

INNER JOIN Table3 T3
ON T2.ColorID=T3.ColorID

INNER JOIN Table4 T4
ON T3.ColorID=T4.ColorID

WHERE T1.itemID between SaleBegin AND SaleEnd


But it's not working for me, instead giving me every possible color and price for each item. What am I doing wrong? How do I get those values? Expected results should look like:

itemID color price
------------------------------------
1 Blue 10
1 Green 13
2 Green 18
2 Blue 14

Answer

http://sqlfiddle.com/#!9/e6fac/4

SELECT t1.itemID
     , t3.color
     , t2.price
  FROM table_1 t1
  LEFT JOIN table_2 t2
    ON t1.itemID = t2.itemID
  LEFT JOIN table_3 t3
    ON t2.colorID = t3.colorID
  LEFT JOIN table_4 t4
    ON t3.colorID = t4.colorID
  WHERE t1.sale_date BETWEEN saleBegin and saleEnd;
Comments