UNION or JOIN?

Dev Shed Forums Sponsor:

March 1st, 2013, 02:01 PM

NewC7

Registered User

Join Date: Mar 2013

Posts: 4

Time spent in forums: 1 h 3 m 10 sec
Reputation 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.

Quote:

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!

March 1st, 2013, 04:00 PM

Problem Solver

Join Date: Jan 2001

Location: Stockholm, Sweden

Posts: 4,430

Time spent in forums: 3 Weeks 4 Days 7 h 23 m 50 sec
Reputation Power: 532

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

__________________
/Stefan