FULL JOIN replacement in mySQL giving problem

my this alternate query for full joint not working

[phparion]

Hi

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, 
payments.paymentDate, payments.amount

FROM orders INNER JOIN payments 

ON orders.orderDate=payments.paymentDate AND orders.customerID =26

UNION ALL

SELECT totalOrderAmount,orderDate, 
CAST(NULL AS SIGNED INTEGER), CAST(NULL as DATE) 

FROM orders WHERE NOT EXISTS (SELECT * FROM payments 
WHERE orders.orderDate = payments.paymentDate)

UNION ALL

SELECT CAST(NULL AS SIGNED INTEGER), CAST(NULL AS DATE),
amount,paymentDate 

FROM payments WHERE NOT EXISTS 
(SELECT * FROM orders WHERE orders.orderDate = payments.paymentDate)

[Dijkgraaf]

There are several issues with your queries.

For the first select you are joining on date from the orders to the payments, but aren't limiting it to the customer on the payments table so you are getting payments from other customers. You can fix that by.

SELECT orders.totalOrderAmount, orders.orderDate,
payments.paymentDate, payments.amount
FROM orders INNER JOIN payments
ON orders.orderDate=payments.paymentDate AND orders.customerID = payment.customerID
WHERE orders.customerID =26

And the subsequent queries you aren't limiting it by customerID at all, so you need to fix those as well.

However I think that's probably still not what you want. If you want all transactions from orders and payment for a client ordered by date then what you probably want is something like

SELECT 'Order' as TranType, orders.totalOrderAmount as Amount, orders.orderDate as TransactionDate
FROM orders
WHERE orders.customerID =26

UNION ALL
SELECT 'Payment' as TranType, payments.amount as Amount, payments.paymentDate as TransactionDate
FROM payments
WHERE payments.customerID =26

ORDER BY TransactionDate

Then based on the TransType you either put the amount and date in the Order columns or the Payment columns.

[phparion]

hats off to you :) that's exactly what I was looking for. In fact I came back to this coding world after three years and have forgotten many concepts. thanks for refreshing my memories :)