want rows in 2nd (joined) table listed as columns with 1st

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'

Thanks
Chris R.

Re: want rows in 2nd (joined) table listed as columns with 1st

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.


//Aho

Re: want rows in 2nd (joined) table listed as columns with 1st

On Sat, 17 Jun 2006 23:53:34 +0200, "J.O. Aho"
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.

Thanks, Chris

Re: want rows in 2nd (joined) table listed as columns with 1st

no@emails.thx wrote:
> On Sat, 17 Jun 2006 23:53:34 +0200, "J.O. Aho"
> 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

Re: want rows in 2nd (joined) table listed as columns with 1st

On Sun, 18 Jun 2006 06:21:26 +0200, "J.O. Aho"
wrote:

>no@emails.thx wrote:
>> On Sat, 17 Jun 2006 23:53:34 +0200, "J.O. Aho"
>> 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.