trouble with dbtype

[throm]

<cfquery name="getIDs" datasource="#request.site.datasource#">
SELECT DISTINCT f.ecrcomplianceid, f.ecractivityid, f.ecrcategoryid,
subtaskid
FROM uspsfat f, uspsecractivity a
WHERE a.ecractivityid = f.ecractivityid and a.ecractivityid = #ID#
</cfquery>

<cfquery name="oaData" dbtype="query" datasource="#request.site.datasource#">
SELECT a.* from uspsecractivity a, query.getIDs g
WHERE a.ecractivityid = g.ecractivityid
order by ecractivity
</cfquery>
This is the code I currently have, it isn't running at all, am I using the
dbtype incorrectly? Any help would be appreciated.

[Dan Bracuk]

When doing a Q of Q, you don't want a datasource attribute in your cfquery tag.

Originally posted by: throm
<cfquery name="getIDs" datasource="#request.site.datasource#">
SELECT DISTINCT f.ecrcomplianceid, f.ecractivityid, f.ecrcategoryid,
subtaskid
FROM uspsfat f, uspsecractivity a
WHERE a.ecractivityid = f.ecractivityid and a.ecractivityid = #ID#
</cfquery>

<cfquery name="oaData" dbtype="query" datasource="#request.site.datasource#">
SELECT a.* from uspsecractivity a, query.getIDs g
WHERE a.ecractivityid = g.ecractivityid
order by ecractivity
</cfquery>
This is the code I currently have, it isn't running at all, am I using the
dbtype incorrectly? Any help would be appreciated.

[paross1]

It isn't very clear at all from your post what you are actually attempting to
accomplish. The query below does what appears like you are trying to do.

<cfquery name="getIDs" datasource="#request.site.datasource#">
SELECT DISTINCT a*, f.ecrcomplianceid, f.ecractivityid, f.ecrcategoryid,
f.subtaskid
FROM uspsfat f
INNER JOIN uspsecractivity a ON a.ecractivityid = f.ecractivityid
WHERE a.ecractivityid = #ID#
</cfquery>

Phil

[throm]

Sorry about the confusion, what I am trying to do is take the ID numbers I get
from one table and use them to get connected columns from a different table.
From what I can understand (which is not much I am pretty new to this query SQL
thing) I need to have 2 different database queries then a final query that
combines the two? This is the code I have right now, still not working. (Hope
the comments make this a bit clearer)

<!--- this query is to get all the different ecrID numbers related to #ID#
--->
<cfquery name="getIDs" datasource="#request.site.datasource#">
SELECT DISTINCT f.ecrcomplianceid, f.ecractivityid, f.ecrcategoryid,
subtaskid
FROM uspsfat f, uspsecractivity a
WHERE a.ecractivityid = f.ecractivityid and a.ecractivityid = #ID#
</cfquery>

<!--- these selects the entire ECR tables --->
<cfquery name="getecractivity" datasource="#request.site.datasource#">
SELECT *
FROM uspsecractivity a
</cfquery>

<cfquery name="getecrcategory" datasource="#request.site.datasource#">
SELECT *
FROM uspsecrcategory
</cfquery>

<cfquery name="getecrcompliance" datasource="#request.site.datasource#">
SELECT *
FROM uspsecrcompliance
</cfquery>

<!--- this gets the columns related to the ID --->
<cfquery name="oaData" dbtype="query">
Select a.*
from getecractivity a, getIDs i
where a.ecractivityid = i.ecractivityid
</cfquery>

<cfquery name="gcData" dbtype="query">
SELECT ca.*
FROM getecrcategory ca, getIDs i
WHERE ca.ecrcategoryid = i.ecrcategoryid
order by ecrcategory
</cfquery>

<cfquery name="ciData" dbtype="query" datasource="#request.site.datasource#">
SELECT co.*
FROM getecrcompliance co, getIDs i
WHERE co.ecrcomplianceid = i.ecrcomplianceid
order by ecrcompliance
</cfquery>

[throm]

I figured it out! Thanks for your time and help.

[paross1]

Sorry, but it is still not clear at all. You can 'take the ID numbers I get
from one table and use them to get connected columns from a different table"
within a single query without having to use Q-of-Q or multiple queries, but you
still haven't really said what specific ID numbers from which tables, etc.

Q-of-Q is usually used to perform a secondary query on the result set from an
original query, and I don't see that as what you are really trying to do. Can
you explain, in English, what your database structure is, the relationships,
and what particular data you are trying to end up with, and what you are trying
to do with that data, etc.?

Phil