Wouldn't it be a lot simpler to get the main group in your first query and forget about the 2nd one?
Yes, except the main group does not exist within the db of the first query
1 person found this helpful
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.
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?
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. ...
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.
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.
database query name = q1
database query name = q2
Q of Q
where q1.something = q2.something
Superficlally reading over this post, are you looking for LEFT|RIGHT OUTER JOIN (in your SQL)?