Thursday, 30 August 2018

How to leave two tables with the same column name

I want to LEFT JOIN two tables with the same column name. I have two tables which I am trying to join but I keep getting an error:

column 'id' is ambiguous

returned in my JSON output.
My current query:
$sQuery = "
 SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
 FROM   $sTable
 LEFT JOIN
    $sTable2
    ON ($sTable2.id = $sTable.id)

 $sWhere
 $sOrder
 $sLimit
";

Produces that error. How can I join these two tables as the join point when there is the same column name in each table?

Be explicit about which table the column belongs to. This also applies to the SELECT part of the query:
SELECT table1.column AS column1, table2.column AS column2
FROM table1
LEFT JOIN table2
ON table1.column = table2.column

To save you some typing time, use table aliases:
SELECT t1.column AS column1, t2.column AS column2
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.column = t2.column

0 comments:

Post a Comment