MySQL: Is there a way to LEFT JOIN conditionally? (or similar?)

Question

I am writing a request to get ALL products in the products table, and the sale price for each IF product for this item has an entry in the specials table.

What I'm looking for is something like:

 SELECT * FROM products P IF (S.specials_date_available <= NOW() AND S.expires_date > NOW()) { // The sale has started, but has not yet expired LEFT JOIN specials S ON P.products_id = S.products_id } 

I understand that MySQL is not a programming language, but is there a way to create a query that will lead to the logical equivalent above?

The result set should look like this:

  ID Name Price Sale Price 1 Widget A 10.00 (empty, because this item has no sale record) 2 Widget B 20.00 15.45 (this item is currently on sale) 3 Widget C 22.00 (empty - this item was on sale but the sale expired) 

Answer 1

Yes, you can move the condition to the JOIN ON part of the request.

 SELECT * FROM products P LEFT JOIN specials S ON P.products_id = S.products_id AND S.specials_date_available <= NOW() AND S.expires_date > NOW() 

Answer 2

 SELECT * FROM products P LEFT JOIN specials S ON P.products_id = S.products_id AND S.specials_date_available <= NOW() AND S.expires_date > NOW()