query questions

[mxuser]

Hi guys i have a peoblem.
i have a database table from which i need to select data.
this is then used to select records from another table.
the results are to be used in a cfmail query using data for another table.
how do you run this type of quey on seperate tables?
how would you suggest how to do this?
i am stumped.

[boughtonp]

More information on how the tables are structured would help, but something
like this will work...

<cfquery name="A" datasource="#Request.Datasource#">
SELECT table_name, column_list, conditions
FROM tableA
</cfquery>
<cfloop query="A">
<cfquery name="InfoQuery" datasource="#Request.Datasource#">
SELECT #column_list#
FROM #table_name#
WHERE #conditions#
</cfquery>
<cfmail to="someone" from="me" subject="#table_name# data" type="HTML">
<cfdump var="#InfoQuery#"/>
</cfmail>
</cfloop>

[Dan Bracuk]

You can do this in one query either by joining on a field, or using a subquery.


This question is very elementary. If you are going to use cold fusion for
anything but your own personal projects, you would be wise to learn sql, and
data modelling.

Originally posted by: mxuser
Hi guys i have a peoblem.
i have a database table from which i need to select data.
this is then used to select records from another table.
the results are to be used in a cfmail query using data for another table.
how do you run this type of quey on seperate tables?
how would you suggest how to do this?
i am stumped.

[mxuser]

thanks for your help apprecited.
however the task is a little more complicated than my original question
reveals.
table a contains useid and selection criteria.
table b contains data to be seelcted fusing table a criteria
thi s may produce 0 or several rows of data fro each row of data from table a.
output then needs to go to table a userid to select email from table b
cfmail it all.
does the logic make sense?
i cant workout the join loop sequence to make this all work
hey ho
and dan everybody starts somehwere thanks