MS Access INNER/LEFT JOIN on NULL values

Question

I have two tables.

The first one is: Project Table

 ID |    SN    | pTypeID

----|----------|-------

 1  | project1 |   1 

 2  | project2 |   3

 3  | project3 |  NULL

 4  | project4 |  NULL

and the second one is: pType Table

 ID |  Name   

----|---------

 1  | pType1 

 2  | pType2 

 3  | ptype3

I want to make a query that will return the following

 ID |    SN    |  Name

----|----------|--------

 1  | project1 | pType1 

 2  | project2 | pType3

 3  | project3 |  NULL

 4  | project4 |  NULL

Here is what I tried:

SELECT Project.ID, Project.SN, pType.Name

FROM Project

INNER JOIN Type ON Project.pTypeID = pType.ID

But I get an error saying "No Value given for one or more parameters".

After some research, I suspect that the error is produced by the NULL values in the Project table. I also tried with Left Joins and got the same error.

I found this question but the solution doesn't work in Access apparently.

I am doing this in Excel with VBA. I am connecting to an Access ".accdb" database using ADODB connections.

Here is my question: is there any efficient way to achieve what I am trying to do?

Thanks

Answer 1

I believe you want to left join on pType

select p.id, p.sn, t.name 
from Project as p 
    left join pType as t on p.pTypeID = t.id;

MS Access INNER/LEFT JOIN on NULL values

Sign up