joined:Nov 9, 2005
I am in a situation where I have two tables Orders and Payments.
In order table I store all customer orders info and in Payments each customer payment history. because we work on LAYAWAYs where customers buy goods and then pay in installments which are not predecided by date or amount.
both table shares common customerID field.
Now I want to make a page where I can see all ORDER-AMOUNTS, PAYMENT-AMOUNTS, ORDER-DATES and PAYMENT-DATES.
it is similar to a manual cash register of a customer where you have two columns, one is for purchase and second for recoveries.
But I am a little confused how to accomplish it with a single query, if possible. I was thinking about a FULL JOIN but that is not supported in MySQL. So I wrote the following query though it gives me unexpected results
SELECT orders.totalOrderAmount, orders.orderDate,
FROM orders INNER JOIN payments
ON orders.orderDate=payments.paymentDate AND orders.customerID =26
CAST(NULL AS SIGNED INTEGER), CAST(NULL as DATE)
FROM orders WHERE NOT EXISTS (SELECT * FROM payments
WHERE orders.orderDate = payments.paymentDate)
SELECT CAST(NULL AS SIGNED INTEGER), CAST(NULL AS DATE),
FROM payments WHERE NOT EXISTS
(SELECT * FROM orders WHERE orders.orderDate = payments.paymentDate)