In a join, how to prefix all column names with the table it came from
mysql, sql
Solution
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.)
Problem
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?