Mysql, addition raws from two different tables, get all the content from the first table and only the curdate content from the second table

Question

I have two different tables a and ah. a is the article stock quantity, ah is the article stock movement.

I would like to have all the rows from a and only the current date (today) movement row from ah and add two columns. I've tried a few things, one of them being right join but I only get articles that had a movement today. Please Could you help?

I've tried this:

SELECT a.numero,
       a.stockDay, 
       date(ah.dateCreation), 
       ah.creationDate, 
       SUM(a.stockDay + ah.diffQuantite) AS 'stockReel' 
FROM article a, articleLotHisto ah 
WHERE ah.articleId = a.id 
AND date(ah.dateCreation) = CURDATE();

Also that:

SELECT a.numero, 
       a.stockDay, 
       date(ah.dateCreation), 
       ah.dateCreation, 
       SUM(a.stockDay + ah.diffQuantite) AS 'stockReel' 
FROM article a
RIGHT JOIN articleLotHisto ah
ON ah.articleId = a.id
WHERE date(ah.dateCreation) = CURDATE();

But I always get only the rows that have been created today and not the complete a table.

Answer 1

Filtering table ah on the date BEFORE joining it to a should do what you want; try this:

SELECT a.numero, 
       a.stockDay, 
       date(ah.dateCreation), 
       ah.dateCreation, 
       a.stockDay + COALESCE(ah.diffQuantite, 0) AS 'stockReel' 
FROM article a
LEFT JOIN ( SELECT *
            FROM articleLotHisto
            WHERE date(dateCreation) = CURDATE()
            ) ah
ON ah.articleId = a.id

Edit: added COALESCE for articles with no movement today.