Join Two Tables based on a third table’s data

I have 3 tables, for the sake of this exercise we’ll call them: Products, Price, and Discount. I’m trying to join Products and Price tables, only if the ProductID is found in Discount.ProductID (ProductID column within the Discount table).

Products:

ProductID Size Color Ref#
A1234 Small Blu 0C94
B5678 Med Red 1D96

Price:

Ref# Base Tax
0C94 3.48 0.96

Discount:

ProductID List Site
A1234 Two Three

I’m familiar with joins, so my code starts off as:

SELECT * FROM Product as a
left join Price as b
on a.Ref# = b.Ref#

but I’ve never nested a constraints within a where clause (if that’s even the correct approach) based on a third table. Any advice would be greatly appreciated. The end result would be a new products table that only shows the one product, because ProductID B5678 is not in the Discount table.

Answer

If you don’t need any of the contents of the Discount table, use the exists() funtion to execute a sub query in the where clause. This will give you the fastest results.

SELECT * 
FROM Product as a
   left join Price as b on a.Ref# = b.Ref#
WHERE EXISTS (
      SELECT *
      FROM Discount as c
      WHERE c.ProductID = a.ProductID
   )

If however you do need one or more of the columns of Discount, do an inner join between Product and Discount, joining them on the ProductID. This will result in only the products that have discount, and then do another left join to Price to get the columns from Price into the resultset too. Do be aware though that in case multiple rows exist in Discount for the one Product row, this will result in the same product shown on multiple rows.

SELECT * 
FROM Product as a
   inner join Discount as c on c.ProductID = a.ProductID
   left join Price as b on a.Ref# = b.Ref#