May 28
SQL select from two tables with the same field name
Running MySQL and doing join'ed queries you might run into problems when you try to get results from two tables into one results set, where both tables have a field that is identically named (common with id-fields). For example:
SELECT table1.id, table1.fieldA, table1.fieldB, table2.id, table2.fieldC, table2.fieldD
FROM `table1`,`table2`
WHERE table1.fieldA = 'test'
AND table1.id = table2.fieldD;
Here we try select'ing in a join'ed manner from two tables and merge the results into one resultsset. But MySQL will throw an error, as both tables have a column named "id".
The solution to this problem is to tell MySQL an alias name for one of the identically named columns so it can differentiate between them in the results set. In our example here, we tell MySQL by using the "AS" constructor to use the obvious name "table1.id" for the "id"-field from table "table1", while it should use the plain "id" for the "id"-field from "table2":
SELECT table1.id AS "table1.id", table1.fieldA, table1.fieldB, table2.id, table2.fieldC, table2.fieldD
FROM `table1`,`table2`
WHERE table1.fieldA = 'test'
AND table1.id = table2.fieldD;