Question 204429 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I've just added a "sum" and a "group by", because we can have more than one movement per day and we want them to be sum up. Here is the query you gave me. Line 18,19,20 is the same article number with each time a -2 quantity move result is 58 it should be 54enter image description here:

    Here I added a sum and a group by the result is correct. Thanks again for your help :-) enter image description here


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search