### Thread: UNION or JOIN?

1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2013
Posts
4
Rep Power
0

#### 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.

Table A
id|amount
100|45.00
102|25.00
100|15.00
101|12.00

Table B
id|amount
100|145.00
102|125.00
100|115.00
101|112.00

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.

SELECT id, SUM(amount) AS total_amount FROM ((SELECT id, amount FROM tableA) UNION (SELECT id, amount FROM tableB)) as x GROUP BY id ORDER BY total_amount DESC LIMIT 10
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!
2. No Profile Picture
sr
Problem Solver
Devshed Specialist (4000 - 4499 posts)

Join Date
Jan 2001
Location
Stockholm, Sweden
Posts
4,480
Rep Power
538
I'm guessing your problem is that you are not using UNION ALL:
Code:
`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`
The normal UNION has an in my opinion odd "feature" that it removes duplicate rows in the same way a SELECT DISTINCT works.

Which would screw up your SUM(amount).
3. Originally Posted by sr
The normal UNION has an in my opinion odd "feature" that ...
it's not odd at all

the correct syntax is
Code:
`UNION [ DISTINCT | ALL ]`
most people never bother to specify the DISTINCT, because that's the default

not odd once you understand
4. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2013
Posts
4
Rep Power
0
UNION ALL looks like it did the trick. THANKS.