Join Two Tables based on a third table's data

Question

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 1

Just do a 3-table join.

SELECT DISTINCT a.*, b.*
FROM Product AS a
JOIN Price AS b ON a.`Ref#` = b.`Ref#`
JOIN Discount AS c ON a.ProductID = c.ProductID