如何创建一个查询以获取一个表中的所有记录并使用 where 语句填充第二个表中的字段信息？

发布于 2024-11-08 01:46:08 字数 1581 浏览 0 评论 0原文

我有两张桌子。一个称为 prem，包含系统中存在的所有 id，而 JimsQueryByColl 则包含其中大部分 id，并附加了各种信息。我正在尝试创建一个包含来自 prem 的所有 id 以及来自 JimsQueryByColl 的所有记录的单个查询，其中 JimsQueryByColl.Collector = "Frederick Road"。我当前的查询如下，但只给出了信息的后半部分（没有来自 prem 的 id，这些 id 没有出现在收集器列中带有 Frederick Road 的 id 列表中）：

选择Prem.meter_miu_id， JimsQueryByColl.*，Prem.经度，前置纬度
从 Prem 左连接 JimsQueryByColl ON Prem.meter_miu_id = JimsQueryByColl。[MIU ID]
在哪里 (((JimsQueryByColl.Collector)="弗雷德里克路”））

通常情况下，Left Join 会实现我想要的效果，但是 where 语句使得除了 *Prem.meter_miu_id* 字段之外的所有 id 都具有空白信息的 ids 不包括在内。我尝试添加一个“ 或 JimsQueryByColl.Collector IS NULL” 到 WHERE 语句，但没有得到正确的结果。

当前用于获取正确信息的两种查询方法的标题为 FredrickRoad 和 Query3：

FredrickRoad-

从其中选择 JimsQueryByColl.* JimsQueryByColl WHERE (((JimsQueryByColl.Collector)="弗雷德里克路”））

随后是 Query3 -

选择Prem.meter_miu_id， JimsQueryByColl.*，Prem.经度， Prem.latitude FROM Prem LEFT JOIN JimsQueryByColl ON Prem.meter_miu_id = JimsQueryByColl.[MIU ID] 哪里 (((JimsQueryByColl.Collector)="弗雷德里克路”));

但如果可能的话，我想一步完成。我希望我已经写清楚了，如果有人需要澄清，请询问。

当前解决方案

选择Prem.meter_miu_id， JimsQueryByColl.*，Prem.经度，前置纬度
进入弗雷德里克路从 Prem 左连接 JimsQueryByColl ON (Prem.meter_miu_id = JimsQueryByColl。[MIU ID]
AND JimsQueryByColl.Collector =“弗雷德里克
路”）

这解决了我在查询中遇到的奇怪错误/错误，因为它将查询放入表中并且不再直接处理查询。但是，我很想知道是什么导致了我遇到的问题拥有。

原文

I have two tables. One is called prem and contains all ids that exist in a system, and JimsQueryByColl which has most of those ids with various information attached. I am trying to create a single query that will include all of the ids from prem, and all of the records from the JimsQueryByColl where JimsQueryByColl.Collector = "Frederick Road". The current query I have is as follows but only gives the latter part of the information (without the ids from the prem that don't appear in a list of ids with Frederick Road in the collector collumn):

SELECT Prem.meter_miu_id,
JimsQueryByColl.*, Prem.longitude,
Prem.latitude
FROM Prem LEFT JOIN
JimsQueryByColl ON Prem.meter_miu_id =
JimsQueryByColl.[MIU ID]
WHERE
(((JimsQueryByColl.Collector)="Frederick
Road"))

Normally the Left Join would have achieved what i desire but the where statement is making it where the ids that would have blank information for all but the *Prem.meter_miu_id* field are not included. I have tried to add an "or JimsQueryByColl.Collector IS NULL" to the WHERE statement but that didn't get the correct result.

The current two query method I have for getting the right information are titled FredrickRoad and Query3:

FredrickRoad-

SELECT JimsQueryByColl.* FROM
JimsQueryByColl WHERE
(((JimsQueryByColl.Collector)="Frederick
Road"))

Followed by Query3 -

SELECT Prem.meter_miu_id,
JimsQueryByColl.*, Prem.longitude,
Prem.latitude FROM Prem LEFT JOIN
JimsQueryByColl ON Prem.meter_miu_id =
JimsQueryByColl.[MIU ID] WHERE
(((JimsQueryByColl.Collector)="Frederick
Road"));

But I would like to do this in one step if possible. I hope I have written this clearly and if someone needs clarification just ask.

Current Solution

SELECT Prem.meter_miu_id,
JimsQueryByColl.*, Prem.longitude,
Prem.latitude
Into FrederickRoad
FROM Prem LEFT JOIN
JimsQueryByColl ON (Prem.meter_miu_id
= JimsQueryByColl.[MIU ID]
AND JimsQueryByColl.Collector="Frederick
Road")

This gets around the odd bug/error I'm getting with the query since it puts it into a table and doesn't deal with the query directly again. I would however love to know what is causing the problems I'm having.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

能否归途做我良人 2024-11-15 01:46:08

尝试：

SELECT Prem.meter_miu_id, JimsQueryByColl.*, Prem.longitude, Prem.latitude
FROM Prem LEFT JOIN JimsQueryByColl 
ON (Prem.meter_miu_id = JimsQueryByColl.[MIU ID] 
AND JimsQueryByColl.Collector="Frederick Road")

Try:

SELECT Prem.meter_miu_id, JimsQueryByColl.*, Prem.longitude, Prem.latitude
FROM Prem LEFT JOIN JimsQueryByColl 
ON (Prem.meter_miu_id = JimsQueryByColl.[MIU ID] 
AND JimsQueryByColl.Collector="Frederick Road")

回复收藏 0 原文

~没有更多了~