Mysql Join Multiple tables

Question

I'm trying to join 5 different tables. They each have a

Name column, Id Column and Exp column

Other than that, there is nothing completely the same about all of them.

SELECT name FROM `weapons`,`arrows`,`trees`,`ores`,`bars` ORDER BY exp DESC

Is all I can come up with, which it says Name is ambiguous. Thanks!

This is like 5 separate user tables that I'm trying to combine into one big list

@AlanBowen please post some sample data and then the desired result, then you will get an answer that can produce the result. — Taryn
– Taryn, Commented Oct 24, 2012 at 17:53
Okay, based on the edit that you want one bit list, you're wanting to UNION the tables, not JOIN, see my answer below. — jmoerdyk
– jmoerdyk, Commented Oct 24, 2012 at 18:05

juergen d · Accepted Answer · 2012-10-24 17:48:49Z

1

If you have columns with the same name you need to add the table name to the column. You can also use an alias name for a table and shorten your query.

If you want to select the name column of the weapons and arrows table do

SELECT w.name, a.name
FROM `weapons` as w, `arrows` as a,`trees` as t,`ores` ass o,`bars` as b
ORDER BY exp DESC

answered Oct 24, 2012 at 17:48

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alan Bowen Over a year ago

None of the tables have the same names.

Taryn Over a year ago

I would place an alias on the column names so you know what table it comes from weapons.name as WeaponsName, arrows.name as ArrowsName

juergen d Over a year ago

@AlanBowen: But the columns have same names. You need to tell the DB engine which name column you want from which table.

Yogendra Singh · Accepted Answer · 2012-10-24 18:07:09Z

0

You may want to use one table prefix to identify the table from which, you want to retrieve name e.g. below:

 SELECT w.name FROM weapons w, arrows a, trees t, ores o, bars b ORDER BY exp DESC

I think you are missing the join conditions as well.

Your query should look like:

SELECT w.name 
FROM weapons w JOIN arrows a
     ON w.id = a.id
     JOIN trees t
     ON a.id = t.id
     JOIN ores o 
     ON t.id = o.id
     JOIN  bars b 
     ON o.id = b.id
ORDER BY exp DESC;

Or if you want to to a union --> combine rows from all tables then :

 SELECT name 
 FROM weapons 
 UNION ALL
 SELECT name 
 FROM arrows
 UNION ALL
 SELECT name 
 FROM trees
 UNION ALL
 SELECT name 
 FROM ores
 UNION ALL
 SELECT name 
 FROM bars;

edited Oct 24, 2012 at 18:07

answered Oct 24, 2012 at 17:49

Yogendra Singh

34.5k7 gold badges66 silver badges73 bronze badges

Comments

jmoerdyk · Accepted Answer · 2012-10-24 17:53:35Z

0

If you're looking for one big list, you want to UNION the tables, not JOIN.

SELECT * FROM weapons
UNION
SELECT * FROM arrows
UNION 
SELECT * FROM trees
UNION 
SELECT * FROM ores
UNION
SELECT * FROM bars;

And if you just want the names, SELECT name instead of SELECT *

answered Oct 24, 2012 at 17:53

jmoerdyk

5,5468 gold badges43 silver badges58 bronze badges

Comments

Taryn · Accepted Answer · 2012-10-24 18:03:05Z

0

If you want one large list, you can use the following:

select name, id, exp, 'weapons' as TableFrom
from weapons
union all
select name, id, exp, 'arrows' as TableFrom
from arrows
union all
select name, id, exp, 'trees' as TableFrom
from trees
union all
select name, id, exp, 'ores' as TableFrom
from ores
union all
select name, id, exp, 'bars' as TableFrom
from bars
order by 3

I added a final column to help identify what table the data is coming from. If you don't want that, then you can drop it.

edited Oct 24, 2012 at 18:03

answered Oct 24, 2012 at 17:54

Taryn

249k57 gold badges374 silver badges409 bronze badges

Collectives™ on Stack Overflow

Mysql Join Multiple tables

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related