Search code examples
sqliteinner-join

SQLLite howto join mutiple tables without column ambiguity

I've the following simplified SQLLite query:

SELECT SPECIALTABLE.DETAILS AS Details 
FROM SPECIALTABLE 
INNER JOIN CUSTOMERTABLE 
ON CUSTOMERTABLE.CUSCODE = SPECIALTABLE.CUSCODE 
INNER JOIN CUSTOMERNAMETABLE 
ON CUSTOMERNAMETABLE.NAMECODE = CUSTOMERTABLE.NAMECODE 
WHERE NAMECODE LIKE '%' LIMIT 10

SPECIALTABLE does not contain the NAMECODE column.

But when I run the query, it gives the "ambiguous column name" error on NAMECODE. I do not want to specify the table however on namecode (e.g. WHERE CUSTOMERTABLE.NAMECODE LIKE ...).

To my understanding, SQLLite should treat the result of these joins like one big table containing all columns that are contained in each of the tables, so why is there ambiguity?

The problem is there is no column directly linking SPECIALTABLE and CUSTOMERNAMETABLE.

Solution

  • When you write your last line, you do not specify what NAMECODE is in condition. So, the SQL can't catch the NAMECODE correctly, because there is two definitions for them (CUSTOMERNAMETABLE.NAMECODE and CUSTOMERTABLE.NAMECODE)

    WHERE NAMECODE LIKE '%' LIMIT 10

    Try to Specify the Table that you want the NAMECODE condition, like this:

    WHERE CUSTOMERNAMETABLE.NAMECODE LIKE '%' LIMIT 10

    Or this:

    WHERE CUSTOMERTABLE.NAMECODE LIKE '%' LIMIT 10