Search code examples
sqlsqliteinner-join

Inner join in SQLite3

I'm trying to execute an inner join in SQLite3 and it's not working. I keep getting error messages indicating that the field name in table2 is ambiguous.

Would appreciate any input as to why.

SELECT 
table1.id, table1.cd1, table1.cd2, table1.cd3, table1.cd4, table2.ab

FROM 
table1

INNER JOIN table2 ON table2.dx = table1.cd1 
INNER JOIN table2 ON table2.dx = table1.cd2
INNER JOIN table2 ON table2.dx = table1.cd3
INNER JOIN table2 ON table2.dx = table1.cd4

GROUP BY 1, 2, 3, 4, 5, 6

The field "dx" in table2 corresponds to four different fields in table1: "cd1", "cd2", "cd3", "cd4."

Solution

  • You need table aliases:

    SELECT t1.id, t1.cd1, t1.cd2, t1.cd3, t1.cd4, ?.ab
FROM table1 t1 JOIN
     table2 t21
     ON t21.dx = t1.cd1 JOIN
     table2 t22
     ON t22.dx = t1.cd2 JOIN
     table2 t23
     ON t23.dx = t1.cd3 JOIN
     table2 t24
     ON t24.dx = t1.cd4 
GROUP BY 1, 2, 3, 4, 5, 6;

    I don't know which table ab is supposed to come from, so you need to fill that in.