Looping through select statements

[r0nke]

Hi all

I need to run a select statement based on the results of another select statement.

this is what I have:

<cfquery name="get_planid" datasource="#Application.dsn#">
SELECT plan_id
FROM VAR6000_partner_PLAN
where
partner_name = 'test'

</cfquery>

this will return:

plan_id
3
10
22


Now, I need to run another statement based on the above plan_ids, 3, 10 and 22.

Like so:

<cfquery name="get_Main_Group" datasource="#Application.dsn#">
SELECT DISTINCT PRODUCT_NAME
FROM VAR6000_PLAN
where plan_id = '#get_planid.plan_id#'
</cfquery>

The only thing is the second query is only running against '3' and not 3, 10 and 22.

I know I need to do some kind of loop but I cant seem to figure it out.

Please help!

 

[NorthStarDA]

i think you need the ValueList function, used like so:

<cfquery name="get_Main_Group" datasource="#Application.dsn#">
  SELECT DISTINCT PRODUCT_NAME
  FROM VAR6000_PLAN
  where plan_id IN '#ValueList(get_planid.plan_id)#'
  </cfquery>

 

[TruthInSatire]

I don't think you need the single quotes in the where clause around your "in" list if they are numeric. if it is text each value must be in quotes. what I do in the case of text (which also works for numbers) is:

<cfset myList = valueList(get_planID.plan_id)>
<!--- output would read 3,10,22 --->

<cfset myList = listChangeDelims(myList, "','")>
<!--- output would read 3','10','22 putting single quotes around #mylist# in the where clause adds the opening and closing single quotes you need --->

<cfquery name="get_Main_Group" datasource="#Application.dsn#">
SELECT DISTINCT PRODUCT_NAME
FROM VAR6000_PLAN
where plan_id IN '#myList#'
</cfquery>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[TruthInSatire]

correction on the syntax again:
where plan_id IN '#myList#'
should read
where plan_id IN ('#myList#')

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[r937]

why bother with two queries and all that IN list manipulation, when you can just ask the database for the final results in one query:

select distinct 
       P.product_name
  from VAR6000_partner_PLAN as PP
inner
  join VAR6000_PLAN as P 
    on PP.plan_id = P.plan_id 
 where PP.partner_name = 'test'

much more efficient

rudy

http://r937.com/

SQL Consulting