如何修复使用内部连接的“含糊的列名错误”错误 [英] How to fix an "ambigous column name error using inner join" error

问题描述

我从数据库中查询，使用两个表，并得到我的问题的标题中描述的错误。在某些情况下，我需要查询的字段是在表A中，但其他的在表B.我动态创建要搜索的列（可以在表A或表B中），我的代码中的WHERE子句导致错误。

I am taking a query from a database, using two tables and am getting the error described in the title of my question. In some cases, the field I need to query by is in table A, but others are in table B. I dynamically create columns to search for (which can either be in table A or table B) and my WHERE clause in my code is causing the error.

有一个动态的方法来解决这个问题，例如如果列在表B中，然后使用表B搜索，或者INNER JOIN应该修复（目前不是）

Is there a dynamic way to fix this, such as if column is in table B then search using table B, or does the INNER JOIN supposed to fix this (which it currently isn't)

表A 字段：id

表B 字段：id

SQL代码

SELECT *
FROM A INNER JOIN B ON A.id = B.id
WHERE 
<cfloop from="1" to="#listLen(selectList1)#" index="i">

    #ListGetAt(selectList1, i)# LIKE UPPER(<cfqueryparam cfsqltype="cf_sql_varchar" value="%#ListGetAt(selectList2,i)#%" />) <!---
                                                    search column name = query parameter

                                                    using the same index in both lists
                                                    (selectList1) (selectList2) --->
    <cfif i neq listLen(selectList1)>AND</cfif> <!---append an "AND" if we are on any but
                                                the very last element of the list (in that
                                                case we don't need an "AND"--->
</cfloop>

这里也提出了问题

以便能够在表A 和表B 中搜索id列中的任何其他字段，作为链接这两个字段的数据。

I would like to be able to search any additional fields in both table A and table B with the id column as the data that links the two.

推荐答案

Employee
------------------
Emp_ID  Emp_Name    Emp_DOB Emp_Hire_Date   Emp_Supervisor_ID


Sales_Data
------------------
Check_ID    Tender_Amt  Closed_DateTime Emp_ID

每个引用的列都应该由表别名;

Every column you reference should be proceeded by the table alias (but you already knew that.) For instance;

SELECT E.Emp_ID, B.Check_ID, B.Closed_DateTime
FROM Employee E 
    INNER JOIN Sales_Data SD ON E.Emp_ID = SD.Emp_ID

但是，当您选择全部以从两个表中获取所有列。让我们看看如下：

However, when you select all (*) it tries to get all columns from both tables. Let's see what that would look like:

SELECT *
FROM Employee E 
    INNER JOIN Sales_Data SD ON E.Emp_ID = SD.Emp_ID

编译器将此视为：

**Emp_ID**, Emp_Name, Emp_DOB, Emp_Hire_Date, Emp_Supervisor_ID, 
Check_ID, Tender_Amt, Closed_DateTime, **Emp_ID**

由于它试图从两个表中获取所有列 Emp_ID 重复，知道哪个Emp_ID来自哪个表，所以你得到使用内部连接的歧义列名错误。

Since it tries to get all columns from both tables Emp_ID is duplicated, but SQL doesn't know which Emp_ID comes from which table, so you get the "ambiguous column name error using inner join".

所以，你不能使用两个表中存在的名称将不明确。

So, you can't use (*) because whatever column names that exist in both tables will be ambiguous. Odds are you don't want all columns anyway.

此外，如果您通过cfloop向SELECT行添加任何列，则必须按表别名继续执行以及。

In addition, if you are adding any columns to your SELECT line via your cfloop they must be proceed by the table alias as well.

- 编辑：我清理了示例并更改了SELECT *拉动第一个表中的所有列到SELECT *拉动两个表中的所有列。 Shawn指出我不正确。

-- I cleaned up the examples and changed "SELECT * pulls all columns from the first table" to "SELECT * pulls all columns from both tables". Shawn pointed out I was incorrect.

这篇关于如何修复使用内部连接的“含糊的列名错误”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！