Join Two Tables based on a third table's data

Issue

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:

Price:

Discount:

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.

Solution

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#

Answered By – rrozema

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0