|
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
SQL concat? help
The syntax you're using is, I think, correlated subquery. Not sureKris Jones 08/04/06 04:00 P What I actually wanted was a list of all someotherID's for a someID in aRuss 08/04/06 04:58 P In CF, getting a list of values from a column in a query is pretty trivial:Kris Jones 08/04/06 05:09 P Yes, but if you have to run a separate query for each row of the parentRuss 08/04/06 05:22 P Russ wrote:Jochem van Dieten 08/05/06 11:24 A
Author: Kris Jones
The syntax you're using is, I think, correlated subquery. Not sure what that concat() function does--maybe that was a UDF that returned an actual list of IDs? In any case, here is a simple join that would return a recordset of related IDs: select s.someid, so.someotherid from mytable s left outer join anothertable so on s.someid=so.somid Cheers, Kris On 8/4/06, Russ <cflists@ruslansivak.com> wrote:
Author: Russ
What I actually wanted was a list of all someotherID's for a someID in a single column. I found a function that sort of does this (Coalesce), and after digging up my old code, did it using temp tables and cursors. I was optimizing code that was doing it in CF and was taking a little too long to run for my taste. Russ
Author: Kris Jones
In CF, getting a list of values from a column in a query is pretty trivial: valuelist(qryname.colname) Cheers, Kris > What I actually wanted was a list of all someotherID's for a someID in a > single column. I found a function that sort of does this (Coalesce), and > after digging up my old code, did it using temp tables and cursors. > > I was optimizing code that was doing it in CF and was taking a little too > long to run for my taste.
Author: Russ
Yes, but if you have to run a separate query for each row of the parent query, that will kill performance.
Author: Jochem van Dieten
Russ wrote: > What I actually wanted was a list of all someotherID's for a someID in a > single column. I found a function that sort of does this (Coalesce), and > after digging up my old code, did it using temp tables and cursors. > > I was optimizing code that was doing it in CF and was taking a little too > long to run for my taste. If your database is extensible you can write your own aggregate function for that. For instance, in PostgreSQL that would be something like: CREATE AGGREGATE gr_aggr ( sfunc = array_append, basetype = anyelement, stype = anyarray, initcond = '{}' ); SELECT id, gr_aggr(val) FROM table GROUP BY id; Jochem
|
Mailing Lists
|
Latest Fusion Authority Articles
|
||||||
