Need SQL Join Help 2

[ToddWW]

I hope this is simple. I have a sports database with a table for teams and a table for games.

Teams
tm_id
tm_name
tm_conf
tm_div

Games
gm_homeid (maps to tm_id in Teams)
gm_oppid (maps to tm_id in Teams)
gm_date
gm_homescore
gm_oppscore

What would a query look like that will give me the conf and div for BOTH teams.

Thanks in advance for your help. Much appreciated.

 

[guelphdad]

Join to the teams table twice.

 

[ToddWW]

Can you help me out on what that looks like? I've never constructed an SQL statement that joins the same table twice. I'm looking for gm_homeid and gm_oppid with tm_div and tm_conf for both gm_homeid and gm_oppid. Thanks a million!

 

[r937]

it looks like this

  FROM games
INNER
  JOIN teams AS home
    ON home.tm_id = games.gm_homeid
INNER
  JOIN teams AS opp
    ON opp.tm_id = games.gm_oppid

then all you need to do is put the table alias prefixes on the columns in the SELECT clause


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ToddWW]

Worked like a charm! Thanks a million!

SELECT Games.gm_date, Games.tm_id, 
	Home.tm_div, Home.tm_conf, Games.gm_oppid,
	Opp.tm_div AS opp_div, 
	Opp.tm_conf AS opp_conf
	FROM Games
	INNER JOIN Teams AS Home
	ON Games.tm_id = Home.tm_id
	INNER JOIN Teams AS Opp
	ON Games.gm_oppid = Opp.tm_id
	WHERE Games.gm_season = 2004
	AND Home.tm_id = 'ARI';

 

[guelphdad]

Todd

Did you mean to only select games where the home team is 'ARI'?

If you need their away games too change the last bit to:
AND (Home.tm_id = 'ARI' OR Opp.tm_id = 'ARI')