Query string results

[CryoGen]

I'm wondering how to run a query using the results of a previous query. I have a page that queries a table on a date range specified by the user and passed to the page via URL variables (see below).

<cfparam name="start_date" default="URL.start_date">
<cfparam name="end_date" default="URL.end_date">

<CFQUERY DATASOURCE="#REQUEST.DataSource#" name="GetMonthList">
  SELECT    report_id, t_date
  FROM      report
  WHERE     t_date BETWEEN #start_date# AND #end_date#
  ORDER BY  t_date
</cfquery>

The IDs for the records that fall within this range are captured in this query (e.g., report_ids 142, 153, 179 and 180 are found in this query).

What I'd like to do is place the results of that query (142, 153, 179, and 180) and put them in the WHERE clause of another query to find all the records in another table that match those IDs. Do I need to incorporate a CFLOOP in the query? How would I do this?

 

[r937]

you don't want to use CFLOOP, not do you want to use a query of query

what you're looking for is a join query

what is the other table which has the ids that you want to match?

r937.com | rudy.ca

 

[CryoGen]

The tables I want to join are:

REPORT
report_id (P)
t_date


TASK
task_id (P)
report_id (F)
project_cat
task

 

[r937]

<cfquery datasource="#request.datasource#" 
 name="[b]combined[/b]">
  select report.report_id
       , report.t_date
       , task.task_id 
       , task.project_cat
       , task.task
    from report
  inner
    join task
      on report.report_id
       = task.report_id
   where report.t_date 
           between #start_date# 
               and #end_date#
  order 
      by report.t_date
       , task.task_id 
</cfquery>

r937.com | rudy.ca

 

[CryoGen]

That did the trick. Thanks R.