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

Question

I'm writing a query to get ALL of the products in the products table, and the sale price for each product IF a record exists for that item 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 realise MySQL is not a programming language, but is there a way to create a query that results in the logical equivalent of the above?

The result set should look like:

 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 query.

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()