VBA中的SQL QUERY(SQL QUERY in VBA)

VBA中的SQL QUERY(SQL QUERY in VBA)

这有什么问题？ 我想列出那些where material number from mm = Child_Material_Number From bom and Parent_Material_Number = ' ' 。

这是我的代码：

rs.open("SELECT Material_Number,Material_Descr_HU FROM mm m" & _ " WHERE " & _ "Child_Material_Number FROM bom" & _ " = Material_Number FROM mm) " & _ "AND Parent_Material_Number = ''", cn, _ ADODB.CursorTypeEnum.adOpenKeyset, _ ADODB.LockTypeEnum.adLockOptimistic _ )

What's the problem with this? I want to list those items where material number from mm = Child_Material_Number From bom and Parent_Material_Number = ' '.

Here is my code:

rs.open("SELECT Material_Number,Material_Descr_HU FROM mm m" & _ " WHERE " & _ "Child_Material_Number FROM bom" & _ " = Material_Number FROM mm) " & _ "AND Parent_Material_Number = ''", cn, _ ADODB.CursorTypeEnum.adOpenKeyset, _ ADODB.LockTypeEnum.adLockOptimistic _ )

最满意答案

在SQL中，子句FROM告诉服务器在搜索数据时要查看哪些表或查询。 您正在寻找的查询是这样的：

SELECT mm.Material_Number, mm.Material_Descr_HU FROM mm INNER JOIN bom ON mm.Material_Number = bom.Child_Material_Number WHERE bom.Parent_Material_Number = ''

另外，始终牢记基本的SQL语句结构，即：

SELECT [fields] FROM [tables or queries] WHERE [condition] ORDER BY [fields]

查询中的问题是您在WHERE子句之后和之内包含FROM子句。 当然，如果你要嵌套查询，你可以这样做 - 以及更多 - 但是你需要第二个SELECT子句。

有很多SQL教程，如果您计划将来使用数据，它们值得一看。

In SQL, the clause FROM tells the server what tables or queries to look into in search of the data. The query you are looking for is something like this:

SELECT mm.Material_Number, mm.Material_Descr_HU FROM mm INNER JOIN bom ON mm.Material_Number = bom.Child_Material_Number WHERE bom.Parent_Material_Number = ''

Also, always have in mind the basic SQL statement structure, which is:

SELECT [fields] FROM [tables or queries] WHERE [condition] ORDER BY [fields]

The problem in your query is that you are including the FROM clause after and within the WHERE clause. Of course, you could do this -and more- if you are nesting queries, but then you'd need a second SELECT clause.

There are plenty of SQL tutorials out there, and they are worth a look if you are planning to work with data in the future.