Wednesday, August 20, 2008

Double inner join

Let's say we have table team, athlete, and player_form_rating. The relationship is: a team has many athletes, a athlete has many player_form_rating(s), when we retrieve record from player_form_rating table, we hope to get all information from athlete and team. We can use double inner join:

select as id1608_0_, as id1604_1_, as id1613_2_,
playerform0_.persona_id as persona5_1608_0_,
playerform0_.athlete_id as athlete6_1608_0_,
playerform0_.week_id as week4_1608_0_,
playerform0_.is_top_pick as is2_1608_0_,
playerform0_.form_rating as form3_1608_0_,
athlete1_.is_active as is2_1604_1_,
athlete1_.overall_value as overall3_1604_1_,
athlete1_.team_id as team9_1604_1_,
athlete1_.jersey_number as jersey4_1604_1_,
athlete1_.first_name as first5_1604_1_,
athlete1_.last_name as last6_1604_1_,
athlete1_.position_id as position8_1604_1_,
athlete1_.common_name as common7_1604_1_,
team2_.default_trans_id as default4_1613_2_,
team2_.espn_id as espn2_1613_2_,
team2_.espn_tag as espn3_1613_2_,
team2_.league_id as league5_1613_2_
ldb_player_form_ratings playerform0_
inner join
ldb_athletes athlete1_
inner join
ldb_teams team2_
and playerform0_.week_id=1

No comments:

Post a Comment