Sherif - 9:42 am on Aug 2, 2010 (gmt 0)

Hey Guys....


First of all i would like to thank each and everyone here in this forum for making it a fruitful place where one can share his knowledge, and help others in their coding helping them achieve their goals.

I am Currently facing a problem in one of My MySQL queries.

I am working on a database that will display user transactions based on the product id.

The products are apartments in a building. So the unique id will be a combination of the Building Number Apartment Number, and the invoice id itself.

The query that i am using is the following:

SELECT invoices.* , payments.* FROM invoices LEFT JOIN payments ON invoices.building_no = payments.building_no AND invoices.apartment_no = payments.apartment_no AND invoices.invoice_id = payments.invoice_id WHERE invoices.building_no = 1 AND invoices.apartment_no = 1 UNION ALL SELECT invoices.* , payments.* FROM invoices RIGHT JOIN payments ON payments.building_no = invoices.building_no AND payments.apartment_no = invoices.apartment_no AND payments.invoice_id = invoices.invoice_id WHERE payments.building_no = 1 AND payments.apartment_no = colname1 AND payments.invoice_id is null ORDER BY invoice_date, payment_date ASC

in the second where statement i used the is null command to avoid the duplicates that will occur due to the union all command.


The output of the query is correct, but the problem is with a segment of the output itself.
for the invoice_id column, the value appears only when there is a match in both tables, and if it is missing in the payments table, then we do not see it anywhere.

I need the invoice id to appear correctly since it will be used for later reference when we want to attach a payment to the invoice itself so that we can compare the values, and dates... etc...

Could you please help me on solving this problem. Your help will be much appreciated.


Sincerely,
Sherif