CODEWITHSUNDEEP
sqlms-access
Jossy
Jossy

Reputation: 999

How to JOIN tables using RIGHT JOIN in MS Access

I have a query with the following SQL:

SELECT tours_atp.NAME_T
      ,today_atp.TOUR
      ,today_atp.ID1
      ,today_atp.ID2
      ,qry_today_Elo_ov_prep_atp.LastOfnElo_Ov AS ID1_nElo_ov
FROM (today_atp INNER JOIN tours_atp ON today_atp.TOUR = tours_atp.ID_T)
INNER JOIN qry_today_Elo_ov_prep_atp ON today_atp.ID1 = qry_today_Elo_ov_prep_atp.ID
WHERE ((tours_atp.RANK_T Between 1 And 4) AND today_atp.RESULT="")
ORDER BY tours_atp.NAME_T;

It works fine.

I now want to build in another table into the query using a RIGHT JOIN as follows:

RIGHT JOIN qry_today_Elo_sur1_prep_atp ON qry_today_Elo_sur1_prep_atp.ID = today_atp.ID1

I've tried adding it onto the end of FROM as follows:

SELECT tours_atp.NAME_T, today_atp.TOUR, today_atp.ID1, today_atp.ID2, qry_today_Elo_ov_prep_atp.LastOfnElo_Ov AS ID1_nElo_ov
FROM ((today_atp INNER JOIN tours_atp ON today_atp.TOUR = tours_atp.ID_T) INNER JOIN qry_today_Elo_ov_prep_atp ON today_atp.ID1 = qry_today_Elo_ov_prep_atp.ID) RIGHT JOIN qry_today_Elo_sur1_prep_atp ON qry_today_Elo_sur1_prep_atp.ID = today_atp.ID1
WHERE (((tours_atp.RANK_T) Between 1 And 4) AND ((today_atp.RESULT)=""))
ORDER BY tours_atp.NAME_T;

However I'm getting a "JOIN expression not supported" error - why would this be? What does the correct syntax look like?

Upvotes: 0

Views: 139

Answers (1)

xQbert
xQbert

Reputation: 35333

Try pasting this into the SQL editor and let MSAccess add the ()'s it wants:

SELECT tours_atp.NAME_T
     , today_atp.TOUR
     , today_atp.ID1
     , today_atp.ID2
     , qry_today_Elo_ov_prep_atp.LastOfnElo_Ov AS ID1_nElo_ov
FROM today_atp 
INNER JOIN tours_atp 
   ON today_atp.TOUR = tours_atp.ID_T
  and tours_atp.RANK_T Between 1 And 4
  and today_atp.RESULT=""
INNER JOIN qry_today_Elo_ov_prep_atp 
   ON today_atp.ID1 = qry_today_Elo_ov_prep_atp.ID
RIGHT JOIN qry_today_Elo_sur1_prep_atp 
   ON qry_today_Elo_sur1_prep_atp.ID = today_atp.ID1
ORDER BY tours_atp.NAME_T

or re-arrange to use a left...

SELECT tours_atp.NAME_T
     , today_atp.TOUR
     , today_atp.ID1
     , today_atp.ID2
     , qry_today_Elo_ov_prep_atp.LastOfnElo_Ov AS ID1_nElo_ov
FROM qry_today_Elo_sur1_prep_atp 
LEFT JOIN today_atp 
   ON qry_today_Elo_sur1_prep_atp.ID = today_atp.ID1
INNER JOIN tours_atp 
   ON today_atp.TOUR = tours_atp.ID_T
  and tours_atp.RANK_T Between 1 And 4
  and today_atp.RESULT=""
INNER JOIN qry_today_Elo_ov_prep_atp 
   ON today_atp.ID1 = qry_today_Elo_ov_prep_atp.ID 
ORDER BY tours_atp.NAME_T

Upvotes: 1

Related Questions