Query of Query between multiple recordsets

[Steve Onnis]

Does anyone know if you can actually query of query between more than one query?

like

<cfquery dbtype="query" name="LOCAL.searchResults">
    SELECT    column
     FROM    query1, query2
     WHERE    query1.id = query2.id
</cfquery>

I am trying to do it but seem to be getting casting issues.  I have tried to case the datatypes to be the same type but still no joy

Steve

[Zac Spitzer]

you definitely can, one workaround for casting issues is to simply
serialise to json and back

> --
> You received this message because you are subscribed to the Google Groups
> "cfaussie" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/cfaussie/-/i9vh8frVHKkJ.
> To post to this group, send email to cfau...@googlegroups.com.
> To unsubscribe from this group, send email to
> cfaussie+u...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/cfaussie?hl=en.



--
Zac Spitzer
Solution Architect / Director
Ennoble Consultancy Australia
+61 405 847 168

[Andrew Myers]

Would using union help? I've done this before with qoq but I'm not sure of its applicable here without seeing your data sets

Sent from my mobile

[Steve Onnis]

i am doing a filtering process combining a cfsearch result set and a result
set from a database query. The KEY value from the cfsearch is matching up
with the primary key value from the db query. i did notice that the key
value from cfsearch is cast as a string where was the id from the db is a
number.

i have tried casting both to NUMBER and as STRING but neither worked and
still got the casting error and also the error saying that the query was
"expecting (like|between)" thing when i try to use "=" in the join

[Steve Onnis]

Finally got this to work.  It seemes i only needed to CAST the value that was a string and not the one that was already a number.