从 1 个以上的表中选择数据 [英] select data from more than 1 table

问题描述

我使用 access 作为我的数据库，并且在尝试让我的查询选择正确的数据时遇到问题.目前我的查询似乎正在返回表中的所有记录，而对于 WHERE 子句中的这个特定值，它应该只返回 1.

I am using access as my db and am having trouble trying to get my query to select the correct data. At the moment my query seems to be returning all records in the tables when really for this particular value in the WHERE clause, it should be returning only 1.

这个想法是根据 WHERE 子句中的值使用请求框"表，并使用这些值从框"表中提取数据.如果有人能指出我的新手错误，我将不胜感激.我想补充一点，我继承了这个数据库并且无法更改它.非常感谢

The idea is to use the 'Requests Boxes' table based on the value in the WHERE clause and use those value to pull data from the 'Boxes' table. I would be grateful if someone could point out my newbie error. I would like to add that I inherited this db and have no option to change it. Many thanks

我的数据库设置如下.

请求框

请求否

盒子

盒子

盒子

客户引用

sql = "SELECT [Request Boxes].[Request no], [Request Boxes].Box, Boxes.Box, Boxes.CustRef " &
                  "FROM [Request Boxes], Boxes WHERE [Request Boxes].[Request no] = '" & item & "'"

更新代码:

sql = "SELECT [Request Boxes].[Request no], [Request Boxes].Box, Boxes.Box, Boxes.CustRef " &
                  "FROM [Request Boxes] INNER JOIN Boxes ON [Request Boxes].Box = Boxes.Box " &
                  "WHERE ((([Request Boxes].[Request no]) = '" & item & "' )" &
                  "AND ([Request Boxes].[Customer] = '" & customer2 & "') AND (Boxes.Status = '" & status & "'))"

推荐答案

您正在使用 FROM 子句连接两个表...

You are joining the two tables with your FROM clause...

FROM [Request Boxes], Boxes

这意味着您将获得所有 Boxes 行，附加到您正确选择的 Request Boxes 行.要挑选Boxes 行匹配，请在FROM...

This means you will get all the Boxes rows, attached to the Request Boxes row you are correctly selecting. To pick out the Boxes row matching, add an ON clause to the FROM...

FROM [Request Boxes], Boxes ON [Request Boxes].[Box] = [Boxes].[Box]

这将确保只有匹配的 Boxes 行附加到 [Request Boxes] 行.

This will ensure that only the matching Boxes row is attached to the [Request Boxes] row.

这篇关于从 1 个以上的表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！