John,
>From my knowledge of SQL, I don't think this can be
easily done. SQL can easily combine output from
different columns from either the same table or
different table, but what you're trying to do is to
combine output from the same column in different ROWS
in the same table.
That being said, what you can do is use a self-join.
One self-join will allow you to pull out two
serviceIDs per user, two self-joins will pull out 3
serviceIDs per user, etc, but as you can see, it
quickly gets unwieldy.
I'd have to have your table structure to offer a more
concrete answer.
--- John Quarto-vonTivadar <[EMAIL PROTECTED]> wrote:
> hello everyone,
> well, it's 1 am and I'm having a brainfart on
> something that is relatively
> simple:
>
> I've got a simple LEFT JOIN, where i retrieve the
> UserID and the ServiceID's
> of Services that User needs. Since they are realted
> through a many-2-many
> relationship, I get something like
>
>
> UserID ServiceID
> 1 402
> 1 417
> 1 533
> 2 200
> 2 201
> 3 {null}
>
> ok, no problem. What I *REALLY* want in the result
> set is for the ServiceID
> to be turned into a list, something like:
>
> UserID ServiceIDs
> 1 402,417,533
> 2 200,201
> 3 {null}
>
> Yeah, it's easy, but inefficient, to do with CF but
> how can I make SQL do
> this for me (esp with the {null})? It's gonna turn
> out to be something easy
> that I've seen before, but I haven't had to write
> something like this for
> like a year and now I'm drawing a one in the morning
> blank. Is it a GROUP BY
> thing? or a sub-query thing?
=====
I-Lin Kuo
Macromedia CF5 Advanced Developer
Sun Certified Java 2 Programmer
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
