Oscar Anthony Oscar Anthony - 1 year ago 130
SQL Question

MS Access INNER/LEFT JOIN on NULL values

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?


Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download