How to do this? results from 2 querys

Hi Phil,

Thank you for your code example.
I will give it a test run later this week and report back!

Thank you both for the reply.
The above code was just a quick example that I typed on the forum, and you are correct with the over use of the extra # around the variables.

To keep things simple (for now) I've imported the "getAccounts" into the same table as ListUsers.
All I need to do now is one query on ListUsers.

For sure I will look into the proper way of doing this when I have some more time.

Once again thank you both for the info.

See Dan's answer for the correct way to solve your problem, but to
provide some basic COMP101 information follow along with the inline
comments.

n03l wrote:
> <cfquery name="getAccounts" datasource="#db2use#">
> SELECT *
> FROM banners
> </cfquery>
>
> <cfoutput query="getAccounts">
> <cfset advertPID = #advertPID#>
> </cfoutput>

This <cfoutput...> block is a loop. It is looping over the record set
returned by the 'getAccounts' query. For each record in the set it as
assigning the value of the 'advertPID' column to the 'advertPID'
variable. When the loop is done the last record's value is now stored
in the advertPID variable.

>
> <cfquery name="ListUsers" datasource="#db2use#">
> SELECT *
> FROM useraccount
> WHERE PIDID = '#advertPID#'
> </cfquery>

This query then uses the current value of the 'advertPID' variable.

>
> <cfoutput query="ListUsers">
> <cfset UserAccount = #name#>
> </cfoutput>

Again you have a loop, placing the each value of the 'name' column into
the 'UserAccount' variable until the loop finishes and the last records
value is stored in the 'UserAccount' variable.

>
> <cfoutput query="getAccounts">
> name: #name# | advertPID: #advertPID# <br />
> </cfoutput>

Now you are looping over the original record set again outputting each
records 'advertPID' column along with the value of 'name' which I do not
know if it is a variable from this record set or set in some code I do
not see.


> Anyone here who can show me how to do this?
> Thank you.

As well as Dan's advice to learn how to write joins in your SQL code to
combine data from two or more table. It looks like you need to brush up
on the concepts of complex variables such as arrays, structures and
record sets. As well as variable scoping as there is some confusing
code in your example where you are using the same variable names in
different scopes but not clearly showing which scope is being used where.

Finally you are over using the ## signs around variables. Generally in
CFML you only need to use ## signs around variables when you want to
output them, not when you use them in assignments or other functions.

Do you know how to select data from more than one table in the same query? If not, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

If you do know how, what is it about this scenario that prevents it?