|
|
Posted by no on 06/18/06 09:54
On Sun, 18 Jun 2006 06:21:26 +0200, "J.O. Aho" <user@example.net>
wrote:
>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 ...
Thanks again ... but I am not sure how that would work when there are
variable numbers of t2 rows. I really want to concat() all related
rows in t2 into a column in a t1 select.
But with version 4 would it be possible to issue a subquery like:
SELECT t1.col1, t1.col2, CONCAT(SELECT '['+t2.col1+']' FROM t2 WHERE
t1.id=t2.parentid) as metadata
Thanks
Chris R.
Navigation:
[Reply to this message]
|