MS Access INNER JOIN 最新条目

发布于 2024-12-02 14:32:02 字数 528 浏览 1 评论 0原文

我在尝试让 Microsoft Access 2007 接受我的 SQL 查询时遇到了一些麻烦，但它不断向我抛出语法错误，而这无助于我纠正问题。

我有两个表，为了方便起见，我们将它们称为“客户”和“订单”。

我需要一些客户详细信息，还需要一些最近订单的详细信息。我目前有一个这样的查询：

SELECT c.ID, c.Name, c.Address, o.ID, o.Date, o.TotalPrice
FROM Customers c
INNER JOIN Orders o
ON c.ID = o.CustomerID
AND o.ID = (SELECT TOP 1 ID FROM Orders WHERE CustomerID = c.ID ORDER BY Date DESC)

对我来说，它似乎有效，但 Access 不断向我抛出“语法错误”，当我点击“确定”时，它选择了一段甚至与它无关的 SQL 文本。

如果我去掉额外的 SELECT 子句，它可以工作，但显然不是我需要的。

有什么想法吗？

原文

I'm having some trouble trying to get Microsoft Access 2007 to accept my SQL query but it keeps throwing syntax errors at me that don't help me correct the problem.

I have two tables, let's call them Customers and Orders for ease.

I need some customer details, but also a few details from the most recent order. I currently have a query like this:

SELECT c.ID, c.Name, c.Address, o.ID, o.Date, o.TotalPrice
FROM Customers c
INNER JOIN Orders o
ON c.ID = o.CustomerID
AND o.ID = (SELECT TOP 1 ID FROM Orders WHERE CustomerID = c.ID ORDER BY Date DESC)

To me, it appears valid, but Access keeps throwing 'syntax error's at me and when I hit OK, it selects a piece of the SQL text that doesn't even relate to it.

If I take the extra SELECT clause out it works but is obviously not what I need.

Any ideas?

分享到QQ

分享到微博

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

发布评论

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

痴情 2024-12-09 14:32:02

在 MS Access 中不能以这种方式使用 AND，请将其更改为 WHERE。此外，列（字段）名称中有两个保留字 - 名称、日期。如果没有表名或别名前缀，或者更好的是重命名，则应将它们括在方括号中。

SELECT c.ID, c.Name, c.Address, o.ID, o.Date, o.TotalPrice
FROM Customers c
INNER JOIN Orders o
ON c.ID = o.CustomerID
WHERE o.ID = (
     SELECT TOP 1 ID FROM Orders 
     WHERE CustomerID = c.ID ORDER BY [Date] DESC)

You cannot use AND in that way in MS Access, change it to WHERE. In addition, you have two reserved words in your column (field) names - Name, Date. These should be enclosed in square brackets when not prefixed by a table name or alias, or better, renamed.

SELECT c.ID, c.Name, c.Address, o.ID, o.Date, o.TotalPrice
FROM Customers c
INNER JOIN Orders o
ON c.ID = o.CustomerID
WHERE o.ID = (
     SELECT TOP 1 ID FROM Orders 
     WHERE CustomerID = c.ID ORDER BY [Date] DESC)

回复收藏 0 原文

白昼 2024-12-09 14:32:02

我弄清楚了如何在 Microsoft Access 中执行此操作。您对预排序的子查询进行INNER JOIN。这样您就不必执行多个不支持的 ON 条件。

SELECT c.ID, c.Name, c.Address, o.OrderNo, o.OrderDate, o.TotalPrice
FROM Customers c
INNER JOIN (SELECT * FROM Orders ORDER BY OrderDate DESC) o
ON c.ID = o.CustomerID

这有多高效是另一个故事，但它有效......

I worked out how to do it in Microsoft Access. You INNER JOIN on a pre-sorted sub-query. That way you don't have to do multiple ON conditions which aren't supported.

SELECT c.ID, c.Name, c.Address, o.OrderNo, o.OrderDate, o.TotalPrice
FROM Customers c
INNER JOIN (SELECT * FROM Orders ORDER BY OrderDate DESC) o
ON c.ID = o.CustomerID

How efficient this is another story, but it works...

回复收藏 0 原文

~没有更多了~