Posted by J.O. Aho on 10/09/86 11:51

no@emails.thx wrote:
> On Sat, 17 Jun 2006 23:53:34 +0200, "J.O. Aho" <user@example.net>
> wrote:
>
>> no@emails.thx wrote:
>>> Hi
>>>
>>> I have 2 tables ("records" and "metadata") that I can join together -
>>> no problem. But I want a select that brings out the columns in table
>>> "records" with the data in the second table also as columns. Like
>>> this:
>>>
>>> "records":
>>> id, person
>>> 1, 'john smith'
>>> 2, 'bill jones'
>>> 3, 'fiona wells'
>>>
>>> "metadata":
>>> id, recordid, data
>>> 1,1,'male'
>>> 2,1,'manager'
>>> 3,1,'BMW hatchback'
>>> 4,2,'male'
>>> 5,2,'cook'
>>> 6,3,'female'
>>> 7,3,'director'
>>> 8,3,'motorbike'
>>> 9,3,'company jet'
>>>
>>> (note there are not always the same number of metadata rows per
>>> record)
>>>
>>> I would like output similar to::
>>> 'john smith', 'male', 'manager', 'BMW hatchback'
>>> 'bill jones', 'male','cleaner'
>>> 'fiona wells', 'female', 'director', 'motorobike', 'company jet'
>> The closest you would get would be using GROUP BY and then use php to process
>> the data and store it in a 2D-array in the form you want the data.
>
> Changing the rules a bit, if I was to accept the "metadata" in a
> single column like:
>
> 'john smith', 'male,manager,BMW hatchback'
> 'bill jones', 'male,cleaner'
> 'fiona wells', 'female,director,motorobike,company jet'
>
> would that be possible to do in a single SELECT? :o)
>
> I could then just parse the last column in PHP.
>

Use CONCAT() to make a string of the data

SELECT t1_column1,t1_column2,...,CONCAT(t2_column1,',',t2_column2,',',...) AS
metadata_column ...


//Aho

[Back to original message]