Left join with condition for right table in mysql

Question

I tried to circle my head around this problem, but I did not make much progress. My goal is to make a left join between two tables with criteria for the correct table. I would like to see a list of all products and prices for the current day, even if there is no price on the current day. Here is a sample code:

SELECT products.id, products.name, prices.price FROM products LEFT JOIN prices ON products.id = prices.id WHERE prices.date = CURRENT_DATE 

Only products with price information for the current date are displayed here.

OK, so this is only the first part of my problem. In the end, I would like to raise the price for CURRENT_DATE + INTERVAL 1 DAY .

Any information would be greatly appreciated.

Answer 1

Assuming PRICES.date is a DATETIME data type, use:

  SELECT pd.id, pd.name, pr.price FROM PRODUCTS pd LEFT JOIN PRICES pr ON pr.id = pd.id AND DATE(pr.date) = CURRENT_DATE 

I used the DATE function to remove the temporary part, because CURRENT_DATE will not include the temporary part, while the DATETIME records will be.

In this example, date criteria are applied before the JOIN is created. It looks like a view, filtering out the information before the JOIN is created - which will lead to different results than if the criteria were specified in the WHERE clause.

For a list of products and prices for tomorrow, use:

  SELECT pd.id, pd.name, pr.price FROM PRODUCTS pd LEFT JOIN PRICES pr ON pr.id = pd.id AND DATE(pr.date) = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) 

Link:

• DATE_ADD

If you want both today's and tomorrow's prices in one request, use:

  SELECT pd.id, pd.name, pr1.price AS price_today, pr2.price AS price_tomorrow FROM PRODUCTS pd LEFT JOIN PRICES pr1 ON pr1.id = pd.id AND DATE(pr1.date) = CURRENT_DATE LEFT JOIN PRICES pr2 ON pr2.id = pd.id AND DATE(pr2.date) = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) 

Answer 2

Strong answers above. Adding OMG Ponies ... to the answer with the “correct” table criteria in the original WHERE statement basically turns the LEFT OUTER JOIN into an INNER JOIN. Just another way of looking at this, that might help.

Answer 3

 SELECT id, name, (SELECT price FROM prices WHERE id = products.id AND prices.date = CURRENT_DATE ) AS price, (SELECT price FROM prices WHERE id = products.id AND prices.date = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) ) AS price_tomorrow FROM products