how to perform inner join on two table where i'am able to retrieve all data?



  • I need help with a MySQL query.

    • bar_opening_details has closing & opening stock
    • bar_recvd_details has received the stock

    When I receive purchased stock, I insert it into bar_recvd_details. Next, I will update close_val in bar_opening_details which is closing stock values of items.

    When I join these two tables together, the number of rows returned from bar_opening_details is equal to the number of rows returned from bar_recvd_details. I.E. the unmatched rows of bar_opening_details are no longer returned.

    The Fiddle is available https://www.db-fiddle.com/f/9SMHPJsPPvkw2WMaQiJAx2/0

    Below is my desired output.

    • I want to join the tables on item_id
    • I want all data from each table, where the = '2019-06-18' from the respective table
    • I also want data from bar_recvd_details, even if recvd_value IS NULL

    bar_opening_details

    SELECT *  FROM bar_opening_details 
    
    item_cid item_id op_value close_val op_date close_date
    2 78 2 88 2019-06-18 2019-06-18
    2 79 28 103 2019-06-18 2019-06-18
    2 97 0 96 2019-06-18 2019-06-18
    6 456 30 48 2019-06-18 2019-06-18
    6 457 130 155 2019-06-18 2019-06-18
    6 484 1 46 2019-06-18 2019-06-18
    6 495 15 61 2019-06-18 2019-06-18
    6 523 1 12 2019-06-18 2019-06-18
    6 529 9 32 2019-06-18 2019-06-18
    6 530 54 98 2019-06-18 2019-06-18
    6 533 0 24 2019-06-18 2019-06-18
    6 630 35 77 2019-06-18 2019-06-18
    6 631 31 122 2019-06-18 2019-06-18
    6 635 5 47 2019-06-18 2019-06-18
    6 636 34 71 2019-06-18 2019-06-18

    bar_recvd_details

    SELECT *  FROM bar_recvd_details 
    
    item_cid item_id recvd_date recvd_value
    6 637 2019-06-19 96
    6 635 2019-06-19 48
    6 637 2019-06-18 96
    6 635 2019-06-18 48
    6 636 2019-06-18 48
    6 457 2019-06-18 48
    6 456 2019-06-18 24
    6 1432 2019-06-18 96
    6 484 2019-06-18 48
    6 523 2019-06-18 12
    6 533 2019-06-18 24
    4 79 2019-06-19 24
    2 78 2019-06-19 24
    6 529 2019-06-18 24

    Two tables Joined

           SELECT 
                bar_opening_details.item_cid,
                bar_opening_details.item_id,
                bar_opening_details.op_value,
                bar_opening_details.close_val,
                bar_opening_details.close_date,
                bar_recvd_details.recvd_value,
                bar_recvd_details.recvd_date,
                bar_recvd_details.item_id
            FROM
                bar_opening_details
                    LEFT JOIN bar_recvd_details 
                    ON bar_recvd_details.item_id = bar_opening_details.item_id 
            where bar_opening_details.close_date = '2019-06-18' 
    
    item_cid item_id op_value close_val close_date recvd_value recvd_date item_id
    2 79 28 103 2019-06-18 24 2019-06-19 79
    2 78 2 88 2019-06-18 24 2019-06-19 78
    2 97 0 96 2019-06-18 (null) (null) (null)
    6 457 130 155 2019-06-18 48 2019-06-18 457
    6 456 30 48 2019-06-18 24 2019-06-18 456
    6 484 1 46 2019-06-18 48 2019-06-18 484
    6 523 1 12 2019-06-18 12 2019-06-18 523
    6 529 9 32 2019-06-18 24 2019-06-18 529
    6 495 15 61 2019-06-18 (null) (null) (null)
    6 533 0 24 2019-06-18 24 2019-06-18 533
    6 530 54 98 2019-06-18 (null) (null) (null)
    6 630 35 77 2019-06-18 (null) (null) (null)
    6 635 5 47 2019-06-18 48 2019-06-19 635
    6 635 5 47 2019-06-18 48 2019-06-18 635
    6 636 34 71 2019-06-18 48 2019-06-18 636
    6 631 31 122 2019-06-18 (null) (null) (null)

    Below is the output I would like

    item_cid item_id op_value close_val close_date recvd_value recvd_date item_id
    2 97 0 96 2019-06-18 (null) (null) (null)
    6 457 130 155 2019-06-18 48 2019-06-18 457
    6 456 30 48 2019-06-18 24 2019-06-18 456
    6 484 1 46 2019-06-18 48 2019-06-18 484
    6 523 1 12 2019-06-18 12 2019-06-18 523
    6 529 9 32 2019-06-18 24 2019-06-18 529
    6 495 15 61 2019-06-18 (null) (null) (null)
    6 533 0 24 2019-06-18 24 2019-06-18 533
    6 530 54 98 2019-06-18 (null) (null) (null)
    6 630 35 77 2019-06-18 (null) (null) (null)
    6 635 5 47 2019-06-18 48 2019-06-18 635
    6 636 34 71 2019-06-18 48 2019-06-18 636
    6 631 31 122 2019-06-18 (null) (null) (null)


  • I want to join the tables on item_id

    You're successfully doing this. You're justing using the wrong type of join.

    I also want data from bar_recvd_details, even if recvd_value IS NULL

    You can't do this with an INNER JOIN. Instead, you need to use a LEFT JOIN to be able to return NULL from a table on the RIGHT side of the JOIN, where no data is present. For example, LEFT JOIN bar_recvd_details b instead of INNER JOIN bar_recvd_details b

    I want all data from each table, where the = '2019-06-18' from the respective table

    You' successfully doing this already. However, when moving to a LEFT JOIN, to meet the above criteria, you need to move that WHERE condition to the JOIN condition. I.E. ON a.item_id=b.item_id AND b.recvd_date = '2019-06-18'

    SELECT a.item_cid,
        a.item_id,
        a.op_value,
        a.close_val,
        a.op_date,
        a.close_date,
        b.recvd_value
    FROM bar_opening_details AS a
        LEFT JOIN bar_recvd_details AS b
        ON a.item_id = b.item_id AND b.recvd_date = '2019-06-18'
    WHERE a.close_date = '2019-06-18';
    
    tem_cid item_id op_value close_val op_date close_date recvd_value
    2 78 2 88 2019-06-18 2019-06-18 null
    2 79 28 103 2019-06-18 2019-06-18 null
    2 97 0 96 2019-06-18 2019-06-18 null
    6 456 30 48 2019-06-18 2019-06-18 24
    6 457 130 155 2019-06-18 2019-06-18 48
    6 484 1 46 2019-06-18 2019-06-18 48
    6 495 15 61 2019-06-18 2019-06-18 null
    6 523 1 12 2019-06-18 2019-06-18 12
    6 529 9 32 2019-06-18 2019-06-18 24
    6 530 54 98 2019-06-18 2019-06-18 null
    6 533 0 24 2019-06-18 2019-06-18 24
    6 630 35 77 2019-06-18 2019-06-18 null
    6 631 31 122 2019-06-18 2019-06-18 null
    6 635 5 47 2019-06-18 2019-06-18 48
    6 636 34 71 2019-06-18 2019-06-18 48

    https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=abede993a01a9b0493c370a2aa33be85




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2