How to fix an "ambigous column name error using inner join" error

Question

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.

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)

Table A fields: id

Table B fields: id

---

SQL code

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>

Question posed here too

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.

Answer 1

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

The compiler sees this as:

**Emp_ID**, Emp_Name, Emp_DOB, Emp_Hire_Date, Emp_Supervisor_ID, 
Check_ID, Tender_Amt, Closed_DateTime, **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.

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.

--Edit: 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.

Answer 2

You have to write your where clause in such a way that you can say A.field_from_A or B.field_from_B. You can always pass A.field_from_A.

Although, you don't really want to say

SELECT * FROM A INNER JOIN B ON A.id=B.id where B.id = '1'.

You would want to say

SELECT * FROM B INNER JOIN A ON B.id=A.id where B.id = '1'

You can get some really slow queries if you try to use a joined table in the where clause. There are times when it's unavoidable, but best practice is to always have your where clause only call from the main table.

Answer 3

When you assemble your select1 variable, add the tablenames or aliases. In other words, instead of resembling this:

select1 = "fred,barney,wilma,pebbles";

Make it resemble this:

select1 = "a.fred,a.barney,b.wilma,b.pebbles";