如何修复“使用内部连接的不明确的列名错误"?错误 [英] 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 是重复的，但是 SQL 不知道哪个 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 * 从两个表中提取所有列".肖恩指出我错了.

-- 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屋！