Queries of Queries

[slanza]

Hi,

I am trying to combine two queries.

The first is a basic query of a table in the database.
<CFQUERY NAME="getStores"
DATASOURCE="#request.ds#"
USERNAME="#request.dsuser#"
PASSWORD="#request.dspass#">
SELECT DealerID,
CompanyName,
Address1,
Address2,
City,
State,
Zip,
Country,
Phone,
Fax,
URL
FROM Dealers
WHERE Zip IN (#ListQualify(ReturnedZIPs,'''',",","ALL")#)
</CFQUERY>

The second is a query that I create on the fly.
<cfset getDistances = #queryNew("Zip, Miles")#>
etc.

I am now trying to merge the results of both into a new query using QofQ but I
keep getting errors.

<CFQUERY NAME="Retailers" DBTYPE="query">
SELECT *
FROM getStores s, getDistances d
WHERE getStores.Zip = getDistances.Zip
</CFQUERY>

If I try this query it tells me that I cannot use '*' on more than one query
(which I don't think is true since it shows it in my CF book).

If I try to write in only one table table name it tells me that the name does
not exist in both queries.

I thought QofQ would merge them even if they didn't have the same columns.

Thanks,
Steve

[slanza]

I figured it out. I guess I needed to scope the variables.
This worked.

<CFQUERY NAME="Retailers" DBTYPE="query">
SELECT getStores.CompanyName AS CompanyName, getStores.Address1 AS
Address1,
getStores.Address2 AS Address2,
getStores.City AS City,
getStores.State AS State,
getStores.Zip AS Zip,
getStores.Country AS Country,
getStores.Phone AS Phone,
getStores.Fax AS Fax,
getStores.URL AS URL,
getDistances.Miles AS Miles
FROM getStores, getDistances
WHERE getStores.Zip = getDistances.Zip
</CFQUERY>