SELECT result with table names for columns

Question

A quick question:

Is it possible to get a MySQL result, with the tablename in front of the column instead of only the column name, when using *?

instead of:

column1, column2, etc

i would need:

table_name.column1, table_name.column2, etc

My problem is, that I join multiple tables togheter, that have columns with the same name (for example, key) but the result of the SELECT will only show one. And typing down every single column, in every joined table, would be a lot more work than simply using * and later get the result with Tablenames.

Is there any solution to this?

Thanks for the help.

If there is, what result would you like to have this obtained as? An array of values such as `'table_name.column1'`? And more importantly, do you need ALL these values? Naming desired columns helps you skip unneccesary data from being fetched. — Robin Castlin, Jul 16 '12 at 11:58
@RobinCastlin Yes, in my case I would have needed all columns - it's too bad that apparently there's no solution for this with `*` - since now I have to get the schema for every table, list every columns of them, and build aliases. Something that really could have been done by mysql whitout me having to write a ton of additional code. — Katai, Jul 16 '12 at 12:01
I'm pretty sure that it's possible through MySQL though. I've run queries through a cms that gave me these values with the table name preffixed, thus allowing same column name twice. — Robin Castlin, Jul 16 '12 at 12:04

score 2 · Accepted Answer · answered Jul 16 '12 at 12:23

I found another solution that actually works as expected!

Apparently, it is possible - at least, with PHP:

$PDO->setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, true);

With this, I get the results back with fully-qualified Column names. I'm really wondering how this looks in SQL, but at least it solves the issue.

Coulndt find it before, thanks to @RobinCastlin for the further link (that brought me to the solution) - and to the rest, for the help!

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

It is not possible with the * selector. This has been already asked about 4 years ago on stackoverflow. You will need to type every column and use aliases.

edited May 23 '17 at 12:20

Community

1
1

answered Jul 16 '12 at 11:54

yan.kun

6,820
2
29
38

score 0 · Answer 3 · answered Jul 16 '12 at 12:02

0

Even though you don't prefer to define all these values, it doesn't have to be hard to do it either:

$arr_keys = array('table_name' => array('column1', 'column2', 'column3'),
                  'table_name2' => array('column1', 'column2', 'column3'));

$arr_values = array();
foreach(array_keys($arr_keys) as $h)
    foreach($arr_keys[$h] as $h2)
        $arr_values[] = "{$h}.{$h2} AS '{$h}.{$h2}'";

$str_values = implode(', ', $arr_values);
# Insert $str_values after your SELECT ...

answered Jul 16 '12 at 12:02

Robin Castlin

10,956
1
28
44

This is what I'll be doing now - sadly, it's not all that easy because the joining tables vary, so I've to handle much more cases. It's a shame that this needs to be done by hand - I dont get why there isnt an option for displaying the source tablename with the column, if it's not a result of a function - something like `*.*` would really have helped - if it would exist – Katai Jul 16 '12 at 12:05
[Try this solution](http://www.php.net/manual/en/function.mysql-fetch-assoc.php#107008) – Robin Castlin Jul 16 '12 at 12:10

score 0 · Answer 4 · answered Jul 16 '12 at 12:18

There is no easy way to get this.

You could use Dynamic SQL (or PHP) to create queries of this type:

SELECT
    table_name.column1 AS `table_name.column1`,
    table_name.column2 AS `table_name.column1`,
...

but is it worth the trouble?

Another solution, that will work only if your tables have no common names in columns, except for the key columns that are used in joining, would be to use NATURAL JOIN:

SELECT
    table_a.a_id
    table_a.column1, 
    table_a.b_id, 
    table_b.b_column2
    table_b.b_column3
    table_b.b_column4
FROM
    table_a JOIN table_b
              ON table_b . b_id = table_a . b_id ;

would become:

SELECT
    *
FROM
    table_a NATURAL JOIN table_b ;

The columns that have same names will be used for the joins and will not be replicated in the results.

The downside is that you should be exttra careful to not have any columns (besides the keys used in joining) and (you and any other developer) not to add any by mistake - as this would break the query, producing not expected results.

SELECT result with table names for columns

4 Answers4

Linked