CODEWITHSUNDEEP
ms-accessleft-joininner-join
Dulini Atapattu
Dulini Atapattu

Reputation: 2735

Left join together with inner join in ms-access

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

Upvotes: 5

Views: 11180

Answers (3)

Aducci
Aducci

Reputation: 26634

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

Upvotes: 2

HansUp
HansUp

Reputation: 97101

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;

Upvotes: 8

Richard Schneider
Richard Schneider

Reputation: 35477

The from and join syntax is wrong.

SELECT School.Name, Student.Name, Grade.Grade
FROM Grade
 left join Student on ...
 left join School on  ...

Where "..." is "<primary key> = <foreign key>".

Upvotes: 0

Related Questions