syber - 5:40 pm on Aug 8, 2010 (gmt 0)
You are not going to get what you want using a JOIN. By design, the columns from the non matching table in an OUTER JOIN will be blank.
Instead, try a subquery.
SELECT invoices.*,(SELECT payment_column
WHERE invoices.building_no = payments.building_no
AND invoices.apartment_no = payments.apartment_no
AND invoices.invoice_id = payments.invoice_id) AS payment_column
WHERE building_no = 1 AND apartment_no = 1
If there is a matching payment, the payment information will show up in the payment column(s).