0

I want to select all columns from a table with one or more joins, but I want to be able to distinguish which table the columns belong to (prepend the column names with the table name). I won't know what columns are in each table so I can't list them manually.

users:
--------------------------------
| id | name | age | contact_id |
--------------------------------
| 18 | Foo  | 21  | 1          |
| 19 | Bar  | 32  | 2          |

contacts:
----------------------------
| id | address  | phone    |
----------------------------
| 1  | 123 Main | 867-5309 |
| 2  | 987 Wall | 555-5555 |

I want to something with:

SELECT * FROM users
JOIN contacts on users.contact_id = contacts.id
WHERE users.id = 18

And get a result like:

---------------------------------------------------------------------
| users.id | users.name | ... | contacts.id | contacts.address | ...
---------------------------------------------------------------------
| 18       | Foo        | ... | 1           | 123 Main         | ...

Attempts

Thus far I've found this to get the column names for each table:

SELECT concat('contacts.', `COLUMN_NAME`) AS 'contacts'
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='myDb' 
  AND `TABLE_NAME`='contacts'

But I don't know how to put that back into my selects or if that's even the right path to go.

Success: My implementation of @Bernd Buffen's answer:

I'm using PHP's PDO class to get my results, so here's how I did it. (I'm ignoring the contacts table for this example as it's easy enough to extrapolate.)

$pdo = new PDO(...);
$stmt = $pdo->prepare(<<< EOT
SELECT group_concat(" ", COLUMN_NAME, " AS 'users.", COLUMN_NAME, "'") as 'columns'
    FROM `INFORMATION_SCHEMA`.`COLUMNS`
    WHERE `TABLE_SCHEMA`='myDb'
        AND `TABLE_NAME`='users'
EOT);
$stmt->execute();
$columns = $stmt->fetch()['columns'];

$stmt2 = $pdo->prepare("SELECT $columns FROM users WHERE id = 18 LIMIT 1");
$stmt2->execute();
$result = $stmt2->fetch();
Phil Tune
  • 3,154
  • 3
  • 24
  • 46
  • You need to use dynamic SQL in a stored procedure to do this. – Barmar Jul 16 '20 at 20:04
  • See https://stackoverflow.com/questions/15507683/how-to-select-column-names-dynamically-in-mysql for a basic example. – Barmar Jul 16 '20 at 20:06
  • @Barmar admittedly I haven't yet grasped stored procedures, but that example doesn't seem to translate to what I'm trying to do. – Phil Tune Jul 16 '20 at 20:29
  • I didn't say it showed how to do exactly what you're trying to do. It shows the general principle of using INFORMATION_SCHEMA to create a query dynamically and then executing that query. – Barmar Jul 16 '20 at 20:30
  • I was hoping you could extrapolate from that to your specific use case. – Barmar Jul 16 '20 at 20:31
  • Change `GROUP_CONCAT(c.COLUMN_NAME)` to `GROUP_CONCAT(c.TABLE_NAME, '.', c.COLUMN_NAME, ' AS \`', c.TABLE_NAME, '.', c.COLUMN_NAME, '\`')` to include the table name. – Barmar Jul 16 '20 at 20:38

1 Answers1

1

Here is a sample with prepared statement

Look at the tables

MariaDB [bernd]> SELECT * FROM users;
+----+------+------+------------+
| id | name | age  | contact_id |
+----+------+------+------------+
| 18 | Foo  |   21 | 1          |
| 19 | Bar  |   32 | 2          |
+----+------+------+------------+
2 rows in set (0.01 sec)

MariaDB [bernd]> SELECT * from contacts;
+----+----------+----------+
| id | address  | phone    |
+----+----------+----------+
|  1 | 123 Main | 867-5309 |
|  2 | 987 Wall | 555-5555 |
+----+----------+----------+
2 rows in set (0.00 sec)

MariaDB [bernd]>

Generate a fieldlist with AS

MariaDB [bernd]> SELECT GROUP_CONCAT(TABLE_NAME,'.',`COLUMN_NAME`," AS '",  
TABLE_NAME,'.', `COLUMN_NAME`,"'" SEPARATOR ', ') 
FROM `INFORMATION_SCHEMA`.`COLUMNS`  
WHERE `TABLE_SCHEMA`='bernd'    
AND `TABLE_NAME` IN ('users','contacts') INTO @allfields;
Query OK, 1 row affected (0.00 sec)

MariaDB [bernd]>

You must change the TABLE_SCHEMA and the tablenames in the IN Claus

Result is stored in @allfields

Now build your query

MariaDB [bernd]> select CONCAT('SELECT ', @allfields, ' FROM users JOIN contacts on users.contact_id = contacts.id WHERE users.id = 18') into @sql;
Query OK, 1 row affected (0.00 sec)

Prepare and execute the statement

MariaDB [bernd]> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [bernd]> EXECUTE stmt;
+-------------+------------------+----------------+----------+------------+-----------+------------------+
| contacts.id | contacts.address | contacts.phone | users.id | users.name | users.age | users.contact_id |
+-------------+------------------+----------------+----------+------------+-----------+------------------+
|           1 | 123 Main         | 867-5309       |       18 | Foo        |        21 | 1                |
+-------------+------------------+----------------+----------+------------+-----------+------------------+
1 row in set (0.00 sec)

MariaDB [bernd]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

MariaDB [bernd]>
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
  • That worked for me! So essentially, I would need to run 5 different statements to get the result? I'm new to prepared statements so took me a little bit of playing with that to realize what it's doing. Thanks so much!! – Phil Tune Jul 16 '20 at 21:18
  • You also can generate the hole Query in 1 step. I did this only in 2 step that you can better read.. And the PREPARE can directliy have a Query. so you need only 3 steps PREPARE, EXECUTE and DELLOCATE – Bernd Buffen Jul 16 '20 at 21:28
  • Would you, possibly, care to show how to do it in 1 step? I'm wanting to run it as a single (or a few) PDO script. Thanks again for taking the time to write all this out; I can grasp a lot more of it with this kind of detail. – Phil Tune Jul 17 '20 at 00:00
  • Doh, a lightswitch just flipped. The term "prepared statements" just went right past me for some reason. Basically get the string from the concatenated `INFORMATION_SCHEMA.COLUMNS` statement, then pass it into the `SELECT @columns FROM my_table`. This is spitting out exactly what I was wanting. I'm a little slow sometimes. – Phil Tune Jul 17 '20 at 01:44