CODEWITHSUNDEEP
mysqlsqldatabase
erogol
erogol

Reputation: 13614

how to fetch matching names to ids on an sql table?

Suppose I have a MATCH table with following shape;

id, home_id, away_id, date, season_id
1,  4,       , 5      xxxx, 3
2,  3,       , 2      xxxx, 4
....

I also have tables for each id column including the names matching the given ids.

TEAMS table

id, name
1, FC Bayern
2, Monaco
...

SEASON table

id, season_name
 1, summer
 2, winter
 ....

So I would like to get the names to first table instead of the ids. What is the most efficient way to do that in a large SQL DB? Final table should print out names on the matching id values.

Upvotes: 0

Views: 342

Answers (2)

juergen d
juergen d

Reputation: 204766

Just join the tables together. Nothing special about it

select m.id, home.name, away.name, m.date, s.name 
from match m
join teams home on m.home_id = home.id
join teams away on m.away_id = away.id
join season s on m.season_id = s.id

Since you need to join teamstwo times, you need to give this table a different alias name.

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726619

You need a join to fetch an item from a different table. Since you have three ids to replace, you need three joins:

SELECT m.id, h.name, a.name, date, s.name
FROM match m
JOIN TEAMS h ON m.home_id = h.id
JOIN TEAMS a ON m.away_id = a.id
JOIN SEASON s ON m.season_id = s.id

The tricky part here is that you are joining to the same table (TEAMS) two times. This is because you need to look up two names from it. When you do joins like that, you give an alias to the table each time that it participates in a join, and then refer to that alias instead of the table name.

In the example above, the TEAMS table has aliases h and a for its two uses in the join - once as the "home" (h) and once as the "away" (a) team lookup table.

Upvotes: 1

Related Questions