MS访问空值上的内部/左连接

时间:2022-05-25 22:34:47

I have two tables.

我有两个表。

The first one is: Project Table

第一个是:Project Table

 ID |    SN    | pTypeID
----|----------|-------
 1  | project1 |   1 
 2  | project2 |   3
 3  | project3 |  NULL
 4  | project4 |  NULL

and the second one is: pType Table

第二个是:pType表

 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.

我用Excel和VBA做这个。我正在连接一个访问”。数据库使用ADODB连接。

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

我的问题是:有没有有效的方法来实现我的目标?

Thanks

谢谢

1 个解决方案

#1


1  

I believe you want to left join on pType

我相信你想在pType上留下join

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

#1


1  

I believe you want to left join on pType

我相信你想在pType上留下join

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