CODEWITHSUNDEEP
mysqlselectjoin
Katai
Katai

Reputation: 2937

SELECT result with table names for columns

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.

Upvotes: 0

Views: 3124

Answers (4)

Katai
Katai

Reputation: 2937

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!

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

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.

Upvotes: 0

Robin Castlin
Robin Castlin

Reputation: 10996

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 ...

Upvotes: 0

yan.kun
yan.kun

Reputation: 6908

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.

Upvotes: 0

Related Questions