Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL alias for SELECT * columns

Tags:

alias

mysql

I'm creating a view that is using data that comes from the same table twice. As a result, same column names appear twice.

Thus, i need to give aliases to these columns. If i were to do it, i'd write it as:

SELECT u.* as 'one_*', u2.* as 'two_*' FROM users u LEFT JOIN relationships r ON u.id=r.id_one LEFT JOIN users u2 ON r.id_two=u2.id 

But that doesn't work. Thanks for your help!

EDIT:

Here's the data i'm actually getting:

|  id  | name |  id  | name |    1     john    2     alex 

Here's the data i'd like to get (while still using a SELECT u.*, u2.*):

|  id  | name |  brother_id  | brother_name |    1     john        2             alex 
like image 793
aaaaaa Avatar asked Dec 01 '11 12:12

aaaaaa


People also ask

Can we use column alias in SELECT statement?

Alias is used to give a temporary name(only for the duration of the query) to the column or table in order to make the column name or table name more readable. It does not change the name of the column permanently. Alias can be performed using the 'AS' keyword or without any keyword.

What is column alias in MySQL?

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.

How do I specify a column alias?

The basic syntax of a table alias is as follows. SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition]; The basic syntax of a column alias is as follows.


1 Answers

You can't use * with an alias. Aliases can be used for individual columns.

You'll have to alias each column instead..

So unfortunately, if you have a lot of columns, you'll need to go:

SELECT u.col1 AS u_col1     , u.col2 AS u_col2     , u.col3 AS u_col3     -- etc     , u2.col1 AS u2_col1     , u2.col2 AS u2_col2     , u2.col3 AS u2_col3     -- etc FROM table1 AS u -- INNER JOIN / LEFT OR RIGHT OUTER JOIN / ,     table2 AS u2 
like image 63
Nonym Avatar answered Nov 09 '22 18:11

Nonym