40

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
aaaaaa
  • 2,588
  • 3
  • 26
  • 31
  • Sorry, I think I'm misunderstanding.. do you need to get each column from both tables returned, or is a UNION like @matthewh provided what you need? – Nonym Dec 01 '11 at 12:17
  • Your example uses an implicit cross join, 99,99% sure that's not what you want. Use explicit SQL '92 join syntax instead. – Johan Dec 01 '11 at 12:24
  • You perfectly understood, but your answer is what i was trying to avoid by using "multiple aliases" :) – aaaaaa Dec 01 '11 at 12:29
  • @Johan there's nothing implicit in my query, i'm just showing you the first part. After that there are two left joins, selecting the right `u2` for each `u` – aaaaaa Dec 01 '11 at 12:32

5 Answers5

31

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
Nonym
  • 6,199
  • 1
  • 25
  • 21
  • 1
    Is there a way to shorten this? I mean... Any way to shorten the amount of syntax where you don't have to say "AS" for every alias? –  Feb 08 '13 at 23:26
  • 2
    You dont need the "AS".. u.col1 u_col1 is fine. Doesn't shorten your effort much, but might make the code easier. – whiteatom Jun 01 '13 at 23:19
1

Try using a UNION query:

e.g.

select a.typeid, a.typename from MYTABLE a where a.typeid=3 UNION select a.typeid, a.typename from MYTABLE a where a.typeid=4

Matt Healy
  • 18,033
  • 4
  • 56
  • 56
  • 2
    I don't think the union will work since he wants to retrieve columns from both instances of the same table, unless I misunderstood... – Nonym Dec 01 '11 at 12:16
  • Ah, perhaps I have misread the question. I think your answer is on the right track. – Matt Healy Dec 01 '11 at 12:18
0

I know this is an old question but I recently had the problem and came up with a solution.

First query the table and get the names of the columns

ex.

"SHOW COLUMNS FROM $table_name"

then use a loop to concat a prefix to the column name

ex

foreach ($all_columns as $the_column){ $alias_select .= ', '.$table_name.'.'.$the_column['Field'].' alias_'.$the_column['Field']; } then just put this string into your query and you will get another set of values all with the prefix_column_name.

Ncoder
  • 131
  • 1
  • 4
0

Can you not just use SELECT * and then in your code refer to u.field1 and u2.field2?

Cylindric
  • 5,858
  • 5
  • 46
  • 68
  • Field names are the same since i'm getting data from the same table twice for one row in the resultset. If i'm running the query without using any aliases, i don't have any way to tell what one column belongs to. – aaaaaa Dec 01 '11 at 13:02
  • You didn't put your whole query, so there was no way of knowing that. Hiding information does limit how much I can help, sorry. – Cylindric Dec 01 '11 at 13:03
  • Yeah, sorry for that. i updated the question, if that can help :) – aaaaaa Dec 01 '11 at 13:07
  • You need to put the whole query you're having trouble with - the most important bit of information, the `FROM ...` is missing. Put something that if copy-and-pasted into a SQL command should work. – Cylindric Dec 01 '11 at 13:38
  • updated again, but i really doubt that'll help for that problem – aaaaaa Dec 01 '11 at 15:01
  • Well, looking at that, have you tried my suggestion? In your PHP or whatever, don't just put `$r['field1']`, put `$r['u.field1']` or `$r['u2.field1']` . – Cylindric Dec 01 '11 at 15:14
  • That's not how mysql works... The rows are always retrieved by column name and not prepended with their table name sadly – Tofandel Oct 29 '19 at 17:31
-1

SELECT alias.* does certainly work in mysql >= 5.6

Max Width
  • 113
  • 2
  • 6
  • 4
    this doesn't address the question... OP wishes to automatically rename the columns with something like the table alias, which doesn't happen with this – Mike M Feb 23 '17 at 01:54