In a join, how to prefix all column names with the table it came from
MysqlSqlMysql Problem Overview
I'm analysing a rather horrible legacy database/codebase, trying to reduce server load by combining queries into joins (including an email alert cron job that typically invokes well over a million separate queries).
SELECT * FROM
class_alerts_holding ah
INNER JOIN class_listings l ON l.id = ah.lid
INNER JOIN class_users u ON u.id = ah.uid
LEFT JOIN class_prodimages pi ON pi.pid = ah.lid
This spits out 120 columns...
aid | id | lid | uid | oid | catName | searchtext | alertfreq | listType | id | owner | title | section | shortDescription | description | featured | price | display | hitcount | dateadded | expiration | url | notified | searchcount | repliedcount | pBold | pHighlighted | notes | ...
To assist my analysis of how to construct the new queries it would be awesome if I could prefix the columns in the result with the table they came from in the JOIN e.g.
class_alerts_holding.aid | class_alerts_holding.id | class_listings.lid | ...
Is there a way to achieve this?
Mysql Solutions
Solution 1 - Mysql
You could
select ah.*, l.*, u.*, pi.* from ...
then the columns will be returned ordered by table at least.
For better distinction between every two sets of columns, you could also add "delimiter" columns like this:
select ah.*, ':', l.*, ':', u.*, ':', pi.* from ...
(Edited to remove explicit aliases as unnecessary, see comments.)
Solution 2 - Mysql
You could name the fields in your query and give them aliases:
SELECT ah.whateverfield1 AS 'ah_field1',
ah.whateverfield2 AS 'ah_field2',
l.whateverfield3 AS 'l.field3',
[....]
FROM class_alerts_holding ah
INNER JOIN class_listings l ON l.id = ah.lid
INNER JOIN class_users u ON u.id = ah.uid
LEFT JOIN class_prodimages pi ON pi.pid = ah.lid
Its a bit of work to manually set up if you have that many fields, but you can simplify this with this query...
SHOW FULL FIELDS FROM your_table_name;
...and a good text editor and copy & paste.
Solution 3 - Mysql
I am convinced that such feature to prefix and/or postfix fields names with a table name in a join SHOULD BE INCLUDED INTO ANSI SQL STANDARD. Currently, in year 2019, there is still no elegant cross-platform way to do it, and all what's left is ugly-looking and error-prone manual hacking with aliases, or platform-specific solutions involving dynamic sql. Everyone would really benefit from having ability to specify custom prefix or/and postfix to fields denoted by 'dot-star' (.*). Sample select after adding such feature would be:
select a.* use prefix,b.* use postfix '_b' from table_a a inner join table_b b on a.id=b.id
As you can see, by default prefix or postfix would equal table name (or alias name), and can be overridden with any desired string literal.
Also what's aching to be added to standard, is ability to exclude certain fields from 'starred' (*) output, which is a shortcut to select all fields. I would add except keyword to list fieds which I do not want to be included for reasons of reducing network data transfer or/and brevity, e.g. :
select * except large_binary_data_field,another_notneeded_field,etc from my_table
Such feature would allow to avoid necessity of explicitly specifying full (and potentially large) list of fields which are needed as opposed to only specifying star and a few fields which are not needed.
So please, whoever reading this post and being able to reach out to ANSI SQL standard influencers, you know what to do )
P.S. yet another ugly, but at least automated & generic dynamic sql wrapper
For the Python advocates who work with psycopg, here is the convenient sub I use (strictly internally, as it's prone to possible sql injections)
def get_table_fields(table,alias,prefix='',suffix='',excluding=''):
if type(excluding)==str: excluding=excluding.split(',')
cur.execute('select * from '+table+' where 0=1');cur.fetchall()
if not (cur.description is None):
return ','.join([alias+'.'+col.name+' '+prefix+col.name+suffix for col in cur.description if not (col.name in excluding)])
And the calling code, where I am joining 3 tables and want to avoid fetching large data field from the datasets table:
sql="""select %s,%s,%s from tasks t,features_sets f,datasets d
where
t.is_active=true and f.is_active=true
and f.task=t.id and t.train_dataset=d.id
""" % (
get_table_fields('tasks','t',prefix='ts_'),
get_table_fields('features_sets','f',prefix='fs_'),
get_table_fields('datasets','d',prefix='ds_',excluding='data')
)
which gets unrolled for me into mighty
select t.id ts_id,t.project ts_project,t.name ts_name,***,
fs_id,f.task fs_task,f.name fs_name,f.description fs_description,***,
d.id ds_id,d.project ds_project,d.name ds_name,***
from tasks t,features_sets f,datasets d
where
t.is_active=true and f.is_active=true
and f.task=t.id and t.train_dataset=d.id
where *** means tons of other useful fields, some of them are common for more than one table (hence the need for prefixing). cur is obviously the psycopg cursor, and 0=1 condition is intended to retrieve only fields names without real data.
Solution 4 - Mysql
The way to dynamically name columns is to generate a prepared statement that references the information_schema. This would give you the results you were looking for.
SET @sql = NULL;
SELECT CONCAT(
'SELECT ',GROUP_CONCAT(c.TABLE_NAME,'.',c.COLUMN_NAME,' AS `',c.TABLE_NAME,'.',c.COLUMN_NAME,'`'),'
FROM class_alerts_holding
INNER JOIN class_listings ON class_listings.id = class_alerts_holding.lid
INNER JOIN class_users ON class_users.id = class_alerts_holding.uid
LEFT JOIN class_prodimages ON class_prodimages.pid = class_alerts_holding.lid'
)
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME IN ('class_alerts_holding','class_listings',
'class_users','class_prodimages');
PREPARE sql_statement FROM @sql;
EXECUTE sql_statement;
The GROUP_CONCAT() function has a default limit of 1024 characters, so depending on the number of columns in your tables, you may need to raise this limit in order to generate the prepared statement.
SET SESSION group_concat_max_len = 1000000;
This command will raise the group concat limit if needed. -
Solution 5 - Mysql
I ended up just building the field set for the query, as as of 2020 this still isn't supported.
But, being a lazy programmer, I obviously didn't want to manually type this all out for all of the tables in my query. So I wrote a query to build the select statement:
SELECT
CONCAT(table_name, ".", column_name, " AS ", CHAR(34), table_name, ".", column_name, CHAR(34)) field_names
FROM
information_schema.columns
WHERE
table_schema = "my_database"
AND table_name IN(
"table_1",
"table_2"
);
which will output something like:
| field_names |
|------------------------------------|
| table_1.id AS "table_1.id" |
| table_1.name AS "table_1.name" |
| table_2.id AS "table_2.id" |
| table_2.number AS "table_2.number" |
That can then easily be copied into your SELECT
statement.
Solution 6 - Mysql
I've found something usefull in this question https://stackoverflow.com/questions/985842/mysql-concat-to-create-column-names . I think that this can be one of the solutions.
Solution 7 - Mysql
Based on the solution proposed by koljaTM and AndriyM, maybe an even better solution is to write your query like this:
select
'--TABLE_AAA:--', TABLE_AAA.*,
'--TABLE_BBB:--', TABLE_BBB.*,
'--TABLE_CCC:--', TABLE_CCC.*,
'--TABLE_DDD:--', TABLE_DDD.*
from ...
Unfortunately this is still not good enough in cases when one (or more) of the tables contains more column names than can fit on the screen width. (So you might see on your screen 20 columns but still not be visible on the screen the name of the table from which they come.)
It would still have been better if SQL provided a way to automatically prefix the column names with the table names...
Solution 8 - Mysql
@alden-w, You may add TABLE_SCHEMA condition to where to do not mix up same table names from different schemas
WHERE c.TABLE_SCHEMA='YOUR_SCHEMA_NAME' AND c.TABLE_NAME IN (....)
Solution 9 - Mysql
CREATE OR REPLACE FUNCTION getAlias (mytable text, my_alias text, my_prefix text)
RETURNS SETOF TEXT AS $$
SELECT my_alias || column_name || ' as ' || my_prefix
FROM information_schema.COLUMNS
WHERE TABLE_NAME = mytable;
$$ LANGUAGE SQL
-- You can write function in db. This function need to be a standard in SQL.
Solution 10 - Mysql
This works for me following DarkRob suggestion in MS SQL. Doing it this way avoids the "Ambiguous column name ..." error message if both tables share some of the column names.
DECLARE @cols1 NVARCHAR(max)
SET @cols1 = (SELECT STUFF(
(SELECT ', ati.' + [COLUMN_NAME] + ' AS ' + 'ati_' + [COLUMN_NAME] FROM information_schema.columns
WHERE [TABLE_NAME] in ('audit_trans_inv') FOR XML PATH('')),1,1,''))
DECLARE @cols2 NVARCHAR(max)
SET @cols2 = (SELECT STUFF(
(SELECT ', ti.' + [COLUMN_NAME] + ' AS ' + 'ti_' + [COLUMN_NAME] from information_schema.columns
WHERE [TABLE_NAME] in ('transaccion_inv') FOR XML PATH('')),1,1,''))
DECLARE @sql NVARCHAR(max) = '
SELECT TOP 5 ' + @cols1 + ',' + @cols2 + '
FROM [millennium].[AUDIT_TRANS_INV] ati
INNER JOIN [millennium].[TRANSACCION_INV] ti ON [ti].[AUDIT_TRANS_INV] = [ati].[AUDIT_TRANS_INV]
EXEC sp_executesql @sql
Solution 11 - Mysql
You may try dynamic sql to create a query on the go as per the table definition.
declare @col varchar(max)
set @col = Select stuff(
(select ', ' + column_name + '.' + table_name
from information_schema.columns
where table_name in ( 'table1', 'table2' ...) for xml
path('')),1,1,'')
declare @query nvarchar(max) = '
select ' + @col + '
from table1
inner join table2 on table1.id = table2.id '
exec sp_executesql @query