CODEWITHSUNDEEP
mysqlsqljoin
John Mathew
John Mathew

Reputation: 405

Query for merging two table data

I have two tables :

stock in

Id---date---Itemname----stockInqty

1 --12/12/2014 ----testitem----12

2 --13/12/2014 ----testitem11----20

and stock out

Id--date--Itemname---stockOutqty

1 --12/12/2014 ----testitem----7

2 --14/12/2014 ----testitem11----15

I need a combined result of testitem record between two date period in the order of 

date----itemname----stockInqty----stockOutqty

12/12/2014--testitem---12----7

13/12/2014--testitem---20----Nil

13/12/2014--testitem---NIL----15

Upvotes: 0

Views: 67

Answers (2)

potashin
potashin

Reputation: 44581

You could use FULL JOIN if it was not MySQL, so you should use LEFT/RIGHT JOIN with UNION :

SELECT t1.`date`
     , t1.itemname
     , t1.stockInqty AS stockInqty
     , t2.stockInqty AS stockOutqty
FROM tbl1 t1 LEFT JOIN tbl2 t2 ON t1.`date` = t2.`date` 
                              AND t1.itemname = t2.itemname
UNION

SELECT t2.`date`
     , t2.itemname
     , t1.stockInqty AS stockInqty
     , t2.stockInqty AS stockOutqty
FROM tbl1 t1 RIGHT JOIN tbl2 t2 ON t1.`date` = t2.`date` 
                               AND t1.itemname = t2.itemname

Upvotes: 3

Joe Taras
Joe Taras

Reputation: 15379

Try this:

SELECT *
FROM StockA
LEFT OUTER JOIN StockB
ON StockA.dataItem = StockB.dataItem
UNION 
SELECT *
FROM StockB
LEFT OUTER JOIN StockA
ON StockA.dataItem = StockB.dataItem

You must use a FULL OUTER JOIN operation

In MySql FULL OUTER JOIN can emulate with two LEFT OUTER JOIN in UNION

Upvotes: 0

Related Questions