Looping over QoQ Where Clause

[fs22]

I have two queries. One is a main query that we pull from a form submission. The second is a small query resultset that lists different subgroups in my company and what main group they belong to. I pull several QoQ from the main query. In the main query results, there is a column that holds the subgroup information. I am trying to do a Q0Q where I group the data from the main query into the main groups for analysis. Here is an example...

Main Query

record 1 - subgroup 1

record 2 - subgroup 2

record 3 - subgroup 3

Second Query

Subgroup 1 - Main Group 1

Subgroup 2 - Main Group 2

Subgroup 3 - Main Group 1

So I am trying to loop over a QoQ on the main query where the result set would contain the information only from Main Group 1. So record 2 would be eliminated. I have tried using an IN clause with a cfloop, but I run into syntax trouble with the comma. I also tried looping over the QoQ as a whole and the resulting dump is only the last record.

If there is anything else you need, let me know.

Any thoughts?

Clay

P.S. Here is a code sampling...

    <cfquery name="rsGroup" datasource="nps">
    SELECT *
    FROM "GROUP"
    WHERE GROUP.PrimaryGroup = '#form.primarygroup#'
    </cfquery>
    rsGroup - <cfdump var="#rsGroup#">

    <cfloop query="rsGroup" startrow="1" endrow="#rsGroup.RecordCount#">
        <cfquery name="rsGroupQoQ" dbtype="query">
        SELECT *
        FROM rsNPS
        WHERE rsNPS.grp = '#rsGroup.group#'
        </cfquery>
    </cfloop>
    rsGroupQoQ - <cfdump var="#rsGroupQoQ#"><cfabort>

[Dan_Bracuk]

Wouldn't it be a lot simpler to get the main group in your first query and forget about the 2nd one?

[fs22]

Yes, except the main group does not exist within the db of the first query

[Dan_Bracuk]

If possible, you should try to do something at the db level so that tables from one db can be selected from the other.

If that's not possible, a single Q of Q that adds the main category to your records from your main query seems like a better approach than using a loop.

[fs22]

Have an example of what you are speaking about? Would you use the QueryAddColumn() function? If so, how do you utilize that to set the values of the column cells based upon another table cell within the row?

[fs22]

ok...I figured it out. I thought I would post my solution in case anyone else runs into this. Also, if anyone out there has a better way, let me know.

<!---Dummy array to house 'blank' value for adding of column to main query--->
<cfset GroupArray = ArrayNew(1)>

<!---Variable that adds mainGroup column to main query with blank data from dummy array--->
<cfset addMainGroup = QueryAddColumn(rsNPS,'mainGroup',GroupArray)>

<!---Loop that sets value on added mainGroup column based off of main query grp column value--->
<cfloop query="rsNPS" startrow="1" endrow="#rsNPS.RecordCount#">
    <cfif rsNPS.GRP EQ "xxxxx"><cfset rsNPS.mainGroup = "yyyyy"></cfif>
    <cfif rsNPS.GRP EQ "xxxxxxxxxx"><cfset rsNPS.mainGroup = "yyyyyyyyyy"></cfif>
    <cfif ...etc. ...
</cfloop>

[Dan_Bracuk]

There is a better way, but it depends on your knowlege of sql.  If you know how to do a database query from more than one table, or a Q of Q from more than one query, look for hints in my earlier answers.

If you don't, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

[fs22]

I have that book and I am familiar with queries from more than one table. The issue in this case is that the data does not exist in the main query. I tried looping over a QoQ and could not get around a syntax error due to commas. If you have a better way, than just point me in the right direction.

[Dan_Bracuk]

database query name = q1

database query name = q2

Q of Q

select somefields

from q1,q2

where q1.something = q2.something

[TLC-IT]

Superficlally reading over this post, are you looking for LEFT|RIGHT OUTER JOIN (in your SQL)?