Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    440
    Thanks
    7
    Thanked 3 Times in 3 Posts

    how to join tables with same field name.

    How do I join two tables that have the same field name and group them so only one of each is returned as results from both tables may have the same `userid`

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,416 Times in 4,381 Posts
    I can't tell from your description whether you really need a JOIN or if you need a UNION.

    I think you need to show us a specific example of what you are after.

    Regardless of whether you need a JOIN or UNION, how will you tell MySQL which one of the pair to pick?

    In other words if I have
    Code:
    -- Table1 --
    userid   frogwarts
       117    greenish pink
       118    black
       119    blue
    
    -- Table2 --
    userid   frogwarts
       116    yellowish mauve
       117    puce
       118    black
    What do you want for the final result? For userids 116, 118, and 119 it is easy. Only one table has userids 116 and 119. And both have the same frogwarts value for userid 188.

    But what do we show for 117? greenish pink or puce??

    How do we tell the computer which one to pick?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Oct 2009
    Posts
    440
    Thanks
    7
    Thanked 3 Times in 3 Posts
    sorry, i mean this...

    -- Table1 --
    userid
    117
    118
    119

    -- Table2 --
    userid
    116
    117
    118

    and return only one of each userid.

    117
    118
    119
    116

  • #4
    Regular Coder
    Join Date
    Oct 2009
    Posts
    440
    Thanks
    7
    Thanked 3 Times in 3 Posts
    think i found it, seems to show the right results.

    SELECT `userid` FROM `table1`
    UNION
    SELECT `userid` FROM `table2`
    ORDER BY `userid`

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,416 Times in 4,381 Posts
    Yes, that works for what you asked for.

    But watch out.

    Suppose you had the tables that I showed:
    Code:
    -- Table1 --
    userid   frogwarts
       117    greenish pink
       118    black
       119    blue
    
    -- Table2 --
    userid   frogwarts
       116    yellowish mauve
       117    puce
       118    black
    If you then did
    Code:
    SELECT userid, frogwarts FROM table1
    UNION
    SELECT userid, frogwarts FROM table2
    You would end up with
    Code:
    userid   frogwarts
       116    yellowish mauve
       117    puce
       117    greenish pink
       118    black
       119    blue
    UNION only gives you a single result from multiple tables if *ALL* the fields from both (or all) tables are the same. If any one field is different, then you get full results from both (or all) tables.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Oct 2009
    Posts
    440
    Thanks
    7
    Thanked 3 Times in 3 Posts
    would this be correct?
    Code:
    SELECT userid FROM table1
    UNION
    SELECT userid FROM table2
    GROUP BY userid
    ORDER BY userid

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,416 Times in 4,381 Posts
    Yes. You don't need the GROUP BY, though. It's not hurting anyting; it's just not doing anything in this particular query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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