Mysql query SELECT with same name fields

Question

I have table1 and table2 in a Mysql database.

Each table has a field with the same name, let's say "id".

I need a query where I can get the "id" field value of both tables. I tried this:

 SELECT 
   table1.id,
   table2.id
 FROM...

But I got an error message:

Unknown column 'table1.id' in 'field list'

John Woo · Accepted Answer · 2012-10-15 01:00:51Z

2

you need to add ALIAS on the columns

 SELECT 
   table1.id AS table1_ID,                              -- keyword AS is optional
   table2.id AS table2_ID
 FROM...

and call their alias (for example) $row["table1_ID"] in PHP .

on more thing Unknown column 'table1.id' in 'field list' results from columns that are unable to be find by the server on your join statements.

follow-up question, can you post the whole query?

answered Oct 15, 2012 at 0:35

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

op's error message would indicate that it's mysql complaining about the unknown field, not PHP, most likely due to a bad/missing join

@MarcB oh! you're exactly right. i was just concerned about the same column names. anyway i will just update the answer.

Thanks for the tip, I can't get it to work so I did something else instead. I'll try this forward. :D