1

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?

Michel Keijzers
  • 15,025
  • 28
  • 93
  • 119
qwerty
  • 575
  • 3
  • 6
  • 8
  • possible duplicate of [PHP & MYSQL: How to resolve ambiguous column names in JOIN operation?](http://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation) – OMG Ponies Jan 27 '11 at 20:31

2 Answers2

7

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>
Chandu
  • 81,493
  • 19
  • 133
  • 134
  • Yes, but what if i want all the columns from both tables? I'd get a pretty long and confusing query if i had to name each column from the tables. The query would also break if i decide to add/rename a column. – qwerty Jan 27 '11 at 20:31
  • @qwerty On the contrary, the query would continue to **work** (i.e. return the same resultset...) if you were to add a column. Renaming columns is a no-go anyway if you want to avoid breaking existing queries. – Jakob Borg Jan 27 '11 at 20:33
  • Well, they would continue working, but i would have to add the new column manually. Correct? I want to avoid that if it's possible. – qwerty Jan 27 '11 at 20:38
  • 1
    It is always a good idea to actually select only columns application needs from specific query, not '*'. – Sergiy Tytarenko Jan 27 '11 at 21:02
  • @qwerty: SELECT * is almost always a bad idea, as it causes a lot of unnecessary network traffic and uses a larger amount of memory in your application. You should always list the specific columns you want returned, and always use a WHERE clause to limit the number of rows returned to as few as possible. Laziness is not an excuse. ;-) – Ken White Jan 27 '11 at 21:19
1

@qwerty

You can write:

SELECT a.*,a.id AS table1_id, b.*,b.id AS table2_id, ....
FROM table1 a, table2 b
WHERE <YOUR CRITERIA>
quinlan
  • 112
  • 1
  • 7