cfloop query not working as expected.

[murpg]

Hi I am trying to create a sitemap from a dynamic web site. I am using 2 cfloop queries to do this. My problem is that on all of the second level items I have the same header ID. How can I fix this? Here is my code. The #t2name# link ID=#qGetSite.T1ID# keeps coming up with 10 it is not looping properly. Any help would be most appreciated.

[Dan_Bracuk]

Why don't you just use one query and join on t1idfk = t1id? Also, in your loop, you have to specify the row number.

[murpg]

Dan, thanks for your suggestions. After beating on it a while I was able to get it to work properly. I would like to add another element to it. Any suggestions on that would help also. I want to add a third table called content whose foreign key is location. This would be part of the ON statement in SQL. ON Tier1ID = Location and ON Tier2ID = Location, so my question is how do I integrate that into my SQL statement for Access and how do I display it? I would want something like this.

Tier1.T1Name
Tier2.T2Name
Mistakenly posted see below.

[murpg]

Dan, thanks for your suggestions. After beating on it a while I was able to get it to work properly. I would like to add another element to it. Any suggestions on that would help also. I want to add a third table called content whose foreign key is location. This would be part of the ON statement in SQL. ON Tier1ID = Location and ON Tier2ID = Location, so my question is how do I integrate that into my SQL statement for Access and how do I display it? I would want something like this.

Tier1.T1Name
Tier2.T2Name
Location
Tier1.T1Name
Location
Tier1.T1Name
Tier1.T1Name
Tier2.T2Name
Tier2.T2Name
Tier2.T2Name

Here is my code.
<CFQUERY NAME="qGetSite" DATASOURCE="#application.ds#">
SELECT * From Tier1 LEFT OUTER JOIN Tier2
ON Tier1.t1id = CInt(Tier2.t1idfk)
UNION
SELECT * From Tier1 RIGHT OUTER JOIN Tier2
ON Tier1.t1id = CInt(Tier2.t1idfk)
ORDER BY Tier1.ButtonLoc, Tier2.ButtonLoc;
</CFQUERY>
<CFOUTPUT QUERY="qGetSite" group="T1Name">
<A HREF="level1.cfm?ID=#qGetSite.T1ID#" CLASS="button" style="width:100%; text-align:left;">#qGetSite.T1Name#</A><br />
<cfoutput>
<cfif qGetSite.T2ID IS NOT ""><A HREF="level2.cfm?ID=#qGetSite.T1ID#&T2ID=#qGetSite.T2ID#" CLASS="buttonlevelsitemap">> #qGetSite.t2name#</a><br /><cfelse></cfif>
</cfoutput>
</CFOUTPUT>

[Dan_Bracuk]

Your sql is much too complicated and you didn't say what content.location is related to. I'll assume you meat that a field in content joins to tier1.t1id. Your sql would simply be:

select t1name, t2name, location
from tier1, tier2, content
where t1id = t1idfk
and t1id = some_field_in_content

to display it do this

<cfoutput query = "theOneAbove">
#t1name#<br />#t2name#<br />#location#<br />

[Sleinaddet]

Seems to me you'd be better combining the queries, sort by Tier1 then by Tier2 and then output using
<CFOUTPUT Query="" Group="Tier1">
#Tier 1#
<CFOUTPUT>
#Tier 2#
</CFOUTPUT>
</CFOUTPUT>
and let CF do the grouping and subheadings