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
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.