Left join together with inner join in ms-access

Question

I have to run the following query in an access database:

    SELECT School.Name, Student.Name, Grade.Grade
    FROM( (`School` `School` LEFT JOIN `Student` `Student` ON `School`.`ID`=`Student`.`SchoolID`) INNER JOIN `Grade` `Grade` ON `Student`.`GradeID` = `Grade`.`ID`)

This gives me the error "Join expression not supported", but if I change the LEFT JOIN to INNER JOIN, it runs correctly. I think the way of using both these joins together on ms-access can have a different way. Can anyone pls tell me what is wrong in this query and/ or the reason that this is not supported.

Thanks

Solution 1

I re-created your query in Access' query designer. The query worked with both joins as INNER, as you found. However, the query designer complained about "ambiguous outer join" when I tried to switch the first INNER JOIN to LEFT JOIN. LEFT for both joins works, as does INNER for both.

If you need LEFT for the first join and INNER for the second, you can move the tables from the second join into a subquery.

SELECT
    School.[Name] AS school_name,
    sub.[Name] AS student_name,
    sub.Grade
FROM
    School LEFT JOIN (
        SELECT
            Student.SchoolID,
            Student.[Name],
            Grade.Grade
        FROM
            Student INNER JOIN Grade
            ON Student.GradeID = Grade.ID) AS sub
    ON School.ID = sub.SchoolID;

Solution 2

Student.GradeID might be null, because you did a LEFT JOIN on Student, it might not exist for your second join.

So basically, whenever you do a LEFT JOIN, you cannot use any of those TABLES columns in future JOINS

Comments

[Richard Schneider]

I don't know you column names for each table, but it would something like: left join Student.ID = Grade.StudentID left join School.ID = Student.SchoolID

[Jeeva J]

Can we avoid subquery in the left join?

[HansUp]

Perhaps School LEFT JOIN (Student INNER JOIN) would do it. Beware Access is demanding about parentheses in the FROM clause. if you're not familiar with the details, suggest you use the query designer to set up your joins.