how to join tables with same field name.

[needsomehelp]

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`

[Old Pedant]

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

-- 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?

[needsomehelp]

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

[needsomehelp]

think i found it, seems to show the right results.

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

[Old Pedant]

Yes, that works for what you asked for.

But watch out.

Suppose you had the tables that I showed:

-- Table1 --
userid   frogwarts
   117    greenish pink
   118    black
   119    blue

-- Table2 --
userid   frogwarts
   116    yellowish mauve
   117    puce
   118    black

If you then did

SELECT userid, frogwarts FROM table1
UNION
SELECT userid, frogwarts FROM table2

You would end up with

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.

[needsomehelp]

would this be correct?

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

[Old Pedant]

Yes. You don't need the GROUP BY, though. It's not hurting anyting; it's just not doing anything in this particular query.