 |
 |
 |
 |
|
|
Date: 05/11/01
- Next message: jomu: "[phplib] Access on an 4D db?"
- Previous message: Layne Weathers: "Re: [phplib] Template: set_file, but not showing up..."
- In reply to: James Carrier: "[phplib] OT: SQL Question"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
On Fri, 11 May 2001, James Carrier wrote:
> Hello
>
> I know this is a bit off-topic but I wonder of any of you can help me with
> this. Let's say I have two tables in mySQL (the types aren't important):
>
> Table info_data:
> - dataid (pk, one to many relationship with same field in next table)
> - title
> - updated
> - notes
>
> Table info_data_fields:
> - fieldid (pk)
> - dataid
> - fieldvalue
>
> How can I get the values from info_data_fields to display in a 'crosstab'
> style with info_data? ie, if I have say 4 entries in info_data_fields, how
> can I get them to display _on the same row_ with info_data? Can I do this
> in pure SQL at all?
>
> Here's the sort of output I want:
>
> dataid title updated fieldvalue1 fieldvalue2 fieldvalue3
> fieldvalue4
>
> 1 test 20010101 apples purple road grass
> 2 random 20010101 oranges green street trees
> 3 new 20010101 pears blue drive shrubbery
select
id.dataid, id.title, id.updated,
idf1.fieldvalue, idf2.fieldvalue, idf3.fieldvalue, idf4.fieldvalue
from
info_data id,
info_data_fields idf1,
info_data_fields idf2,
info_data_fields idf3,
info_data_fields idf4
where
id.dataid=idf1.dataid
and id.dataid=idf2.dataid
and id.dataid=idf3.dataid
and id.dataid=idf4.dataid
...or something along these lines, I think.
Hope this helps !
M.
>
> Instead of this, which I already know how to do via JOINing the tables:
>
> titile fieldvalue
>
> test apples
> test purple
> test road
> test grass
> random oranges
> random green
>
> etc.
>
> I hope that's clear - I've been staring at this for a while now. Any help
> would be gratefully received!
>
> Many thanks
>
> james
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: phplib-unsubscribe <email protected>
> For additional commands, e-mail: phplib-help <email protected>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: phplib-unsubscribe <email protected>
For additional commands, e-mail: phplib-help <email protected>
- Next message: jomu: "[phplib] Access on an 4D db?"
- Previous message: Layne Weathers: "Re: [phplib] Template: set_file, but not showing up..."
- In reply to: James Carrier: "[phplib] OT: SQL Question"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]