MySQL Join Help

seanmt · #1 (**permalink**) 05-24-10, 19:33

I am wanting to get data from one table using information from one. How would I go about doing this?

The ftv_match table has a home team and an away team id which relate to the ftv_team table. So what I want to be able to do is get the team names for both of them from the ftv_team table. The code I have at the moment only gets the name for the home team. What do I need to change to get the name for the away team too?

Code:

SELECT * FROM ftv_match
JOIN ftv_team
ON ftv_match.home = ftv_team.id

Example row from ftv_match

Code:

id	home	away	competition	date		time	channel
5545	243	433	12		2010-07-03	124500	2

Example row from ftv_team

Code:

id	name
243	Team X
433	Team Y

r937 · #2 (**permalink**) 05-24-10, 19:54

Code:

SELECT m.id
     , m.competition 
     , m.date  
     , m.time 
     , m.channel
     , h.name AS home_team_name
     , a.name AS away_team_name
  FROM ftv_match AS m
INNER
  JOIN ftv_team AS h
    ON h.id = m.home
INNER
  JOIN ftv_team AS a
    ON a.id = m.away

seanmt · #3 (**permalink**) 05-25-10, 07:03

Brilliant, thanks for the quick response as well!

Internet Services

Web Development

Digital Marketing

Consumer Tech