homepage Welcome to WebmasterWorld Guest from 174.129.103.100
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
FULL JOIN replacement in mySQL giving problem
my this alternate query for full joint not working
phparion




msg:4473639
 12:00 pm on Jul 8, 2012 (gmt 0)

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




msg:4473709
 12:23 am on Jul 9, 2012 (gmt 0)

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




msg:4473922
 9:43 pm on Jul 9, 2012 (gmt 0)

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 :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved