Matthew,
Your could merge the two ideas with the following:
<cfquery name="spListNarrow" datasource="#main_dsn#">
select
*
from
table2
where
id IN (
select
distinct id
from
table1
)
</cfquery>
Though, it sounds like you are just doing a table join where the
following may be more simple:
<cfquery name="spListNarrow" datasource="#main_dsn#">
select
*
from
table2
join table1 on
table2.id = table1.id
</cfquery>
On Tue, Feb 15, 2011 at 3:40 PM, Matthew Nicholson
<[email protected]> wrote:
> Afternoon All!
>
>
>
> I seem to be running into a bit of a syntax error for query within a query.
>
>
>
> Here’s the situation,
>
>
>
> I’ve got a query that creates contains an idea with an associated display
> variable. I’d like to take that query and use QoQ limit the results based
> upon a separate select statement or a list in a where clause.
>
>
>
> Approach 1: (Nested Select)
>
> <cfquery dbtype="query" name="spListNarrow">
>
> select *
>
> from query
>
> where id in
>
> (
>
> select distinct id
>
> from table
>
> )
>
> </cfquery>
>
>
>
>
>
> Approach 2: (Create the List and try to use it)
>
> <cfquery name="qryGetID" datasource="#main_dsn#" dbtype="ODBC">
>
> select distinct id from table
>
> </cfquery>
>
>
>
> <CFSET IDs = ValueList(qryGetID.id, ", ")>
>
>
>
> <cfquery dbtype="query" name="spListNarrow">
>
> select *
>
> from query
>
> where id IN ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#IDs#"
> list="yes"> )
>
> </cfquery
>
>
>
> Approach 1 fails as it seems to ignore the results returned from the nested
> select and Approach 2 has issues due to date types.
>
>
>
> Any thoughts or suggestions would be greatly appreciated, I feel like I’m
> missing something quite simple in all of this.
>
>
>
> Thanks!
>
>
>
> Matthew R. Nicholson
>
> To find what you seek in the road of life, the best proverb of all is that
> which says: "Leave no stone unturned."
> ~Edward Bulwer Lytton
>
>
>
> -------------------------------------------------------------
> To unsubscribe from this list, manage your profile @
> http://www.acfug.org?fa=login.edituserform
>
> For more info, see http://www.acfug.org/mailinglists
> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> List hosted by FusionLink
> -------------------------------------------------------------
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
