Fun with MySQL

The problem: Given a pair of tables in a header-detail relationship (or any one-to-many relationship), write a query that returns one row for each header row, including a column that is a comma-delimited vector of the subkeys that are found in the detail rows (for instance, line item numbers). For example:

Table INVOICE

Invoice  Date
-------  ----
AZ456    1-Jan-2010
AZ457    2-Jan-2010
AZ459    2-Jan-2010

Table INVOICE_LINE

Invoice  Line  Part
-------  ----  ----
AZ456    1     ZQX3
AZ456    2     WF612
AZ457    1     TY301
AZ459    3     TY301

Colleague Jared gave me this screwdriver from the MySQL toolbox: the GROUP_CONCAT() function.

SELECT I.Invoice, I.Date, GROUP_CONCAT(IL.Line) AS Vector
FROM INVOICE I
INNER JOIN INVOICE_LINE IL ON IL.Invoice = I.Invoice
GROUP BY I.Invoice

The results:

Invoice  Date        Vector
-------  ----        ------
AZ456    1-Jan-2010  1,2
AZ457    2-Jan-2010  1
AZ459    2-Jan-2010  3

In particular, we used this query to produce a table of photo image assets retrieved from the CMS (that met certain search criteria against the metadata like caption, photographer, agency). Each image asset has one or more crops in various aspect ratios (standard 4:3, square, wide 16:9). The search results table includes a vector of which crops are available for each image asset. In the UI, this vector is rendered as a nifty, compact row of gray and black icons designed by colleague Vincent.

This technique would work for any other attributes of the detail table, not just keys, in which case you may want to add the DISTINCT keyword to GROUP_CONCAT's argument. The SEPARATOR clause can overide the default comma delimiter.