CODEWITHSUNDEEP
mysql
Alan Bowen
Alan Bowen

Reputation: 1048

Mysql Join Multiple tables

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

Upvotes: 1

Views: 157

Answers (4)

Yogendra Singh
Yogendra Singh

Reputation: 34397

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;

Upvotes: 0

Taryn
Taryn

Reputation: 247880

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.

Upvotes: 0

jmoerdyk
jmoerdyk

Reputation: 5528

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 *

Upvotes: 0

juergen d
juergen d

Reputation: 204924

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

Upvotes: 1

Related Questions