Results 1 to 7 of 7

Thread: Help with UNION

  1. #1
    Join Date
    Jul 2010
    Posts
    11

    Unanswered: Help with UNION

    I have 4 tables, basically two tables of stocks and two tables of reps.

    I need to first UNION table StocksA and StocksB
    and UNION table RepsA and RepsB.
    Once I have these two result tables, I need to output StockName and RepName and INNER JOIN these two tables so that kCode matches. How do I do this in one SELECT statement? See examples below.
    I know this should be simple but I can't figure it out! Help!!!

    Table StocksA
    --------------------
    kCode, StockName
    --------------------------------
    INTC Intel
    CSCO Cisco
    MSFT Microsoft

    Table StocksB
    kCode, StockName
    --------------------------------
    VMW VMware
    CTXS Citrix

    Table RepsA
    kCode, RepName
    ------------------------
    CSCO John
    MSFT Bill

    Table RepsB
    kCode, RepName
    ------------------------
    INTC Jane
    VMW Joseph


    RESULT Table should have these.
    StockName, RepName
    -------------------------
    Intel Jane
    Cisco John
    Microsoft Bill
    VMware Joseph

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Post what you've got so far and we'll help tweak it. Like you say it is not hard but verbose so you'll probably be 90% there.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Posts
    11
    I am guessing something like this. However, this INNER JOIN first and then do UNION?
    How do I do it the other way around, UNION the tables first then INNER JOIN?
    Is there a better way?

    SELECT StocksA.StockName, RepsA.RepName
    FROM StocksA INNER JOIN
    RepsA ON StocksA.kCode = RepsA.kCode
    UNION
    SELECT StocksB.StockName, RepsB.RepName
    FROM StocksB INNER JOIN
    RepsB ON StocksB.kCode = RepsB.kCode

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Firstly, use UNION ALL (check the difference between UNION and UNION ALL and chose the appropriate one, though it probably should be UNION ALL).

    you need to create a derived table. Air code
    Code:
    SELECT *
    FROM (SELECT foo FROM bar
    UNION ALL
    SELECT foo FROM baz) AS my_union
    INNER JOIN pew
    ON pew.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2010
    Posts
    11
    It didn't quite work. Where do I union the Reps tables in the syntax?

    SELECT *
    FROM (SELECT kCode, StockName
    FROM StocksA
    UNION ALL
    SELECT kCode, StockName
    FROM StocksB) AS my_union
    INNER JOIN (SELECT *
    FROM RepsA
    UNION ALL
    SELECT *
    FROM RepsB)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rhap4boy View Post
    It didn't quite work. Where do I union the Reps tables in the syntax?
    You have corrected my mistake - I missed that there were two resp tables.

    All you need now is:
    Code:
    .... AS resp_union
    ON resp_union.kCode = my_union.kCode
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2010
    Posts
    11
    Thank you so much!!! It worked!!!

    Code:
    SELECT     my_union.StockName, resp_union.RepName
    FROM         (SELECT     kCode, StockName
                           FROM          StocksA
                           UNION ALL
                           SELECT     kCode, StockName
                           FROM         StocksB) AS my_union INNER JOIN
                              (SELECT     kCode, RepName
                                FROM          RepsA
                                UNION ALL
                                SELECT     kCode, RepName
                                FROM         RepsB) AS resp_union ON resp_union.kCode = my_union.kCode

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •