Is it possible to rename the columns of a resultset?

Question

I have two tables joined on an ID.

they might look like this:

table_overview  
| id | version | description |
------------------------------
| 1  | 1.2.3   | new stuff.. |


table_files
| id | type    | file_name   |
------------------------------
| 1  | TEXT    | as.txt      |
| 1  | IMG     | abc.png     |
| 1  | AUDIO   | def.wav     | 

Where the primary key of table_overview is id and the primary key of table_files is id and type.

I want to return a result set where each row contains all of the information, but replace the heading file_name with the type instead, so a row in the result set would look something like this:

| id | version | description | text   | img     | audio   |
| 1  | 1.2.3   | new stuff.. | as.txt | abc.png | def.wav |

Is something like this possible? Or am I better off simply creating the table that way in the first place.
The initial idea was that it should be easy to add new types in the future.

Answer 1

If you have a fixed set of properties and there will be at most one value for each entity then you can do:

SELECT o.id, o.version, o.description
     , t.file_name AS text
     , i.file_name AS img
     , a.file_name AS audio
FROM   table_overview AS o
LEFT OUTER JOIN 
       table_files AS t ON t.id=o.id AND t.type = 'TEXT'
LEFT OUTER JOIN 
       table_files AS i ON i.id=o.id AND i.type = 'IMG'
LEFT OUTER JOIN 
       table_files AS a ON a.id=o.id AND a.type = 'AUDIO'

or if there will always be a row for each property (i.e. the row will exist with a NULL value if there is no value, instead of not having a row for the property at all) for each entity using inner joins may be more efficient:

SELECT o.id, o.version, o.description
     , t.file_name AS text
     , i.file_name AS img
     , a.file_name AS audio
FROM   table_overview AS o
JOIN   table_files AS t ON t.id=o.id AND t.type = 'TEXT'
JOIN   table_files AS i ON i.id=o.id AND i.type = 'IMG'
JOIN   table_files AS a ON a.id=o.id AND a.type = 'AUDIO'

If there might be more than one of a property for any given entity (for example if there could be multiple image files for one overview row) then this will get more complicated.

Using GROUP BY and aggregates can change the performance for the better too, as mentioned by Raymond, depending on how many properties there are, what other filtering clauses are in your queries, and how your tables are indexed. In many cases it will be better, but in some it will be noticeably worse (particularly when filtering on one of the transposed properties or picking a few properties from a bag that can contain many per item) due to causing scans where a small number of seeks may otherwise be sufficient:

SELECT o.id, o.version, o.description
     , MAX(IF(type = 'TEXT' , file_name, NULL)) AS text
     , MAX(IF(type = 'IMG'  , file_name, NULL)) AS img
     , MAX(IF(type = 'AUDIO', file_name, NULL)) AS audio
FROM   table_overview AS o
LEFT OUTER JOIN
       table_files AS f ON f.id=o.id
GROUP BY o.id, o.version, o.description

You'll need to test/benchmark your application to see if this method is faster or not.

In other databases like SQL Server there are PIVOT features that make this sort of thing less hassle, but IIRC mySQL doesn't yet have such features.

If you don't have a fixed set of properties that you need to transpose then you are forced to use ad-hoc SQL either generated by your application layer or via stored procedure (see http://buysql.com/mysql/14-how-to-automate-pivot-tables.html for one example of the latter). This is usually the case even in DBs with PIVOT/UNPIVOT/equivelant too as such features are generally implemented with fixed properties/columns in mind.