[SOLVED] Can I get all this info in One query

[jdubwelch]

I have two tables: "bowl_games" and "teams"

 

table "bowl_games:

 

table "teams":

 

I'm trying to select the following with only query if possible:

 

    bowl_games.bowl_game_id, bowl_games.name, bowl_games.date,

 

    AND

 

    teams.team_id, teams.name, teams.record, teams.rank  (from the home_id & away_id associated to the bowl_game_id)

 

 

This is the query I used, but as you can see I'm getting the bowl_game info twice.

 

SELECT bg.bowl_game_id, bg.name, bg.date, t.team_id, t.name 
FROM `bowl_games` AS `bg`, `teams` AS `t` 
WHERE bg.away_id = t.team_id OR bg.home_id = t.team_id

 

 

Is it possible?  What would you do?

 

[BenInBlack]

try this

select bg.bowl_game_id,bg.name,bg.date,t1.team_id as Away_Id,t1.name as Away_Name,t2.team_id as Home_Id,t2.Name as Home_name
from bowl_games bg
left outer join teams t1 on t1.team_id = bg.away_id
left outer join teams t2 on t2.team_id = bg.home_id

 

[jdubwelch]

that worked!  thanks so much!!!