Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
March 1st, 2013, 03: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?

#2
March 1st, 2013, 05:00 PM
 sr
Problem Solver

Join Date: Jan 2001
Location: Stockholm, Sweden
Posts: 4,486
Time spent in forums: 3 Weeks 4 Days 16 h 51 m 51 sec
Reputation Power: 533
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

#3
March 1st, 2013, 05:28 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,824
Time spent in forums: 3 Months 1 Week 4 Days 9 h 14 m 21 sec
Reputation Power: 4208
Quote:
 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
__________________
r937.com | rudy.ca

#4
March 1st, 2013, 08:30 PM
 NewC7
Registered User

Join Date: Mar 2013
Posts: 4
Time spent in forums: 1 h 3 m 10 sec
Reputation Power: 0
UNION ALL looks like it did the trick. THANKS.

 Viewing: Dev Shed Forums > Databases > MySQL Help > UNION or JOIN?