0

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 free column displays 10 instead of 2, since the free column in purchase_invoice contains 2 for the invoice_no 1 ?

  • How I get the output as free col's output as 2 instead of 10?

2
  • and invoice_no=9 how this correlates with column free contains 2 for the invoice_no 1.? Commented Jun 24, 2015 at 7:03
  • sorry, forget to edit the question. Now, corrected. Commented Jun 24, 2015 at 7:04

1 Answer 1

1

Because 'free' column's sequence mismatch in purchase_invoice query. Query should be:

SELECT item_name,batch_no,invoice_no,invoice_date,qty,qty,unit_vat,unit_discount,unit_price,packing,free FROM purchase_invoice AS D

I placed 'free' column at the end of select sequence.

Sign up to request clarification or add additional context in comments.

1 Comment

is that the order important?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.