0

I want to use inner join and right join statement. enter image description here

This is the relation of my tables. I'm trying but it says join statement is not supported.

Here's my code:

SELECT ProjectName, HoursWorked, FirstName, LastName
FROM (PROJECT AS P INNER JOIN ASSIGNMENT AS A ON P.ProjectID = A.ProjectID)
RIGHT JOIN EMPLOYEE AS E ON A.EmployeeNumber = E.EmployeeNumber

Someone use right join statement iteratively but it didnt' work for me. Ms API says both left and right join can also use with inner join. Why it didn't work?

Yoo Inhyeok
  • 101
  • 1
  • 2
  • 9

1 Answers1

1

Try using RIGHT JOIN instead of INNER JOIN in the sub select:

SELECT ProjectName, HoursWorked, FirstName, LastName
FROM (PROJECT AS P RIGHT JOIN ASSIGNMENT AS A ON P.ProjectID = A.ProjectID)
RIGHT JOIN EMPLOYEE AS E ON A.EmployeeNumber = E.EmployeeNumber

From the documentation:

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • It didn't work. It has error, but it's Korean.. I don't know how to translate this. – Yoo Inhyeok Apr 08 '17 at 09:17
  • I guess `P.ProjectID = A.ProjectID RIGHT JOIN EMPLOYEE AS E ON A.EmployeeNumber = E.EmployeeNumber access syntax error (missing operator) in query expression` – Yoo Inhyeok Apr 08 '17 at 09:18
  • @YooInhyeok Try using `RIGHT JOIN` everywhere. – Tim Biegeleisen Apr 08 '17 at 09:53
  • Ms Access supports right join. Right join works well. I chekced. But failed on that syntax – Yoo Inhyeok Apr 08 '17 at 14:24
  • I'm sorry I confused. But why right join not supported? I mean, `SELECT ProjectName, FirstName, LastName, HoursWorked FROM (PROJECT AS P INNER JOIN ASSIGNMENT AS A ON P.ProjectID = A.ProjectID) LEFT JOIN EMPLOYEE AS E ON A.EmployeeNumber = E.EmployeeNumber ORDER BY P.ProjectID, A.EmployeeNumber; ` works – Yoo Inhyeok Apr 09 '17 at 10:19
  • but `SELECT ProjectName, FirstName, LastName, HoursWorked FROM (PROJECT AS P INNER JOIN ASSIGNMENT AS A ON P.ProjectID = A.ProjectID) RIGHT JOIN EMPLOYEE AS E ON A.EmployeeNumber = E.EmployeeNumber ORDER BY P.ProjectID, A.EmployeeNumber;` NOT. – Yoo Inhyeok Apr 09 '17 at 10:20
  • The only difference of them is INNER / RIGHT JOIN. – Yoo Inhyeok Apr 09 '17 at 10:20