CODEWITHSUNDEEP
sqlms-access
gadss
gadss

Reputation: 22489

multiple select in access SQL

I have 3 tables... Records, Teams and Referees in my MS-Access in the Teams table i have

Team_ID, Team_Name

in the Referees Table i have

Referee_ID and Referee_Name

In Records Table i have

Game_ID, Game_No, Play_Date, Win_Team, Lose_Team, Referee_1, Referee_2

Win_Team and Lose_Team from Records is Foreign Key to Team_ID in Teams Table. and Referee_1 and Referee_2 is Foreign Key to Referee ID from Referees Table.

My question is that it is possible that i can query the Team_Name of the Records.Win_Team and Records.Lose_Team? also with the Referees...

i try this one,

SELECT distinct Records.Game_No, Records.Play_Date, IIf([Records.W_Team]=[Teams.Team_ID],Teams.Team_Name) AS Expr1, IIf([Records.L_Team]=[Teams.Team_ID],Teams.Team_Name) AS Expr2
FROM Records, Teams 
where Records.W_Team = Teams.Team_ID
or Teams.Team_ID = Records.L_Team
group by
Records.Game_No,
Records.Play_Date,
Records.W_Team,
Teams.Team_ID,
Teams.Team_Name,
Records.L_Team

but the output is enter image description here

do anyone have an idea about my situation? thanks in advance...

Upvotes: 1

Views: 1928

Answers (1)

Heinzi
Heinzi

Reputation: 172200

You need to join the Teams twice. Just imagine that you have two Teams tables: Join one to the winner ID and one to the loser ID:

SELECT Records.Game_No, Records.Play_Date, 
       Team_Winners.Team_Name, Team_Losers.Team_Name
  FROM (Records INNER JOIN Teams AS Team_Winners
        ON Records.Win_Team = Team_Winners.Team_ID)
       INNER JOIN Teams AS Team_Losers
       ON Records.Lose_Team = Team_Losers.Team_ID

The same is true for the referees, you also need to join them twice (this is left as an exercise ;-)).

Upvotes: 2

Related Questions