combine two tables using the below select query:
SELECT item_name,batch_no,invoice_no,invoice_date,damaged_qty,qty,unit_vat,unit_discount,unit_price,packing,free FROM
(SELECT item_name,batch_no,invoice_no,invoice_date,damaged_qty,qty,unit_vat,unit_discount,unit_price,packing,free
FROM damage_stocks AS S UNION ALL
SELECT item_name,batch_no,invoice_no,invoice_date,qty,qty,unit_vat,free,unit_discount,unit_price,packing
FROM purchase_invoice AS D) AS alias_table
GROUP BY invoice_no,item_name,batch_no
HAVING COUNT(*)=1 and invoice_no=1
ORDER BY item_name,batch_no,qty;
Resultant output :
'MM TONE_', '1', '1', '2015-06-24', 10, 10, 0.00, 2.00, 0.00, 20.00, '10'
You see, it displays free as 10 but in my damage_stocks table, free column contains no value and in the purchase_invoice table, the column free contains 2 for the invoice_no 1.
select free from purchase_invoice where invoice_no = 1;
output:
2
this
select free from damage_stocks where invoice_no = 1;
returns an empty value, because there are no values are added.
And my question is,
Why
freecolumn displays 10 instead of 2, since the free column inpurchase_invoicecontains2for theinvoice_no1 ?How I get the output as
freecol's output as 2 instead of 10?
and invoice_no=9how this correlates withcolumn free contains 2 for the invoice_no 1.?