Results 1 to 3 of 3

Thread: MySQL Join Help

  1. #1
    Join Date
    May 2010
    Posts
    2

    Unanswered: MySQL Join Help

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2010
    Posts
    2
    Brilliant, thanks for the quick response as well!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •