MySQL: SELECT * FROM table1, table2.... column1 AS newColumnName

sql mysql mysql-error-1052
qwerty picture qwerty · Jan 27, 2011 · Viewed 12.4k times · Source

I'm selecting data from two tables. And both of those tables have an ID column, and i need both of the ID columns returned after executing the Query. Is there any way to change the name of the ID column (from the second table i'm selecting from) to something else using AS?

I'm thinking something like this:

SELECT * FROM table1, table2 WHERE table2.id AS newAlias

Can I use the WHERE statement like that?

Answer

Chandu picture Chandu · Jan 27, 2011

You need to specify the alias for the column in the select list. e.g:

SELECT a.id AS table1_id, b.id AS table2_id, ....
  FROM table1 a, table2 b
 WHERE <YOUR CRITERIA>

Related questions

1052: Column 'id' in field list is ambiguous

I have 2 tables. tbl_names and tbl_section which has both the id field in them. How do I go about selecting the id field, because I always get this error: 1052: Column 'id' in field list is ambiguous Here's my …

Read More
MySQL - Selecting data from multiple tables all with same structure but different data

Ok, here is my dilemma I have a database set up with about 5 tables all with the exact same data structure. The data is separated in this manner for localization purposes and to split up a total of about 4.5 million …

Read More
How to resolve ambiguous column names when retrieving results?

I have two tables in my database: NEWS table with columns: id - the news id user - the user id of the author) USERS table with columns: id - the user id I want to execute this SQL: SELECT * …

Read More