March 1st, 2013, 02:01 PM
UNION or JOIN?
Hi Guys/Gals. I'm new to the forum and have a question about a complex query I need to achieve. I'm new to UNIONS and JOINS.
I have two tables each having payment data in it that I need to create a top 10 list.
What I need is this:
1. 100 - 320.00
2. 102 - 150.00
3. 101 - 124.00
Sort would be in the SUM total amount. The two tables in question do not have every column the same, they don't even have the same number of columns. Both have an ID and AMOUNT column though that are identical.
This is what I'm trying but it's not giving me the correct totals.
Do you see anything wrong above? Would it be the columns in the two tables not being identical? If so, is there another query to achieve the same results?
Thanks for any help you can provide!
March 1st, 2013, 04:00 PM
I'm guessing your problem is that you are not using UNION ALL:
The normal UNION has an in my opinion odd "feature" that it removes duplicate rows in the same way a SELECT DISTINCT works.
SELECT id, SUM(amount) AS total_amount FROM ((SELECT id, amount FROM tableA) UNION ALL (SELECT id, amount FROM tableB)) as x GROUP BY id ORDER BY total_amount DESC LIMIT 10
Which would screw up your SUM(amount).
March 1st, 2013, 04:28 PM
it's not odd at all
the correct syntax is
most people never bother to specify the DISTINCT, because that's the default
UNION [ DISTINCT | ALL ]
not odd once you understand
March 1st, 2013, 07:30 PM
UNION ALL looks like it did the trick. THANKS.