Thread: UNION or JOIN?

    #1
  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. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. 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.

IMN logo majestic logo threadwatch logo seochat tools logo