CODEWITHSUNDEEP
sqlms-access
Will Palfrey
Will Palfrey

Reputation: 19

SELECT from multiple tables in SQL

I am trying to select data from 2 tables, Game and Team. I have a list of games in the Game table, where there are columns named htID, atID, apiID and a few others; and I would like to grab the names of these teams from the Team database.

I am currently using multiple SQL statements, which work in theory but not in my program due to the fact that I cannot open more than one database reader at a time. 

SELECT * FROM Game WHERE Played = 0 ORDER BY DT ASC
SELECT TN FROM Team WHERE apiID = htID 
SELECT TN FROM Team WHERE apiID = atID

(where htID and atID are variables in my program containing the ID's grabbed from the first SQL result)

Is there any way I can do the above with only one statement?

Upvotes: 1

Views: 2581

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You need two joins. In MS Access tis requires over-user of parentheses:

select g.*, th.tn as htn, ta.tn as atn
from (game as g inner join
      team as th
      on g.htID = th.apiID
     ) innerjoin
     team as ta
     on g.atID = ta.apiID
where g.played = 0
order by date desc;

Upvotes: 2

Related Questions