Looping or SQL problem

[devmonk]

I have a template the creates an xml file from a series of DB queries. Everything is working fine but it is repeating the list of products once for each item in the list. For example instead of getting:

<Designer>
<Title>David Yerman</Title>
<Products>
<Product>
<LargeView>yurman/l/img1.jpg</LargeView>
<SmallView>yurman/s/img1.jpg</SmallView>
</Product>
<Product>
<LargeView>yurman/l/img2.jpg</LargeView>
<SmallView>yurman/s/img2.jpg</SmallView>
</Product>
</Products>
<Designer>

I get the following:

<Designer>
<Title>David Yurman</Title>
<Products>
<Product>
<LargeView>yurman/l/img1.jpg</LargeView>
<SmallView>yurman/s/img1.jpg</SmallView>
</Product>
<Product>
<LargeView>yurman/l/img2.jpg</LargeView>
<SmallView>yurman/s/img2.jpg</SmallView>
</Product>
<Product>
<LargeView>yurman/l/img1.jpg</LargeView>
<SmallView>yurman/s/img1.jpg</SmallView>
</Product>
<Product>
<LargeView>yurman/l/img2.jpg</LargeView>
<SmallView>yurman/s/img2.jpg</SmallView>
</Product>
<Product>
<LargeView>danielk/l/img1.jpg</LargeView>
<SmallView>danielk/s/img1.jpg</SmallView>
</Product>
<Product>
<LargeView>danielk/l/img2.jpg</LargeView>
<SmallView>danielk/s/img2.jpg</SmallView>
</Product>
<Product>
<LargeView>danielk/l/img1.jpg</LargeView>
<SmallView>danielk/s/img1.jpg</SmallView>
</Product>
<Product>
<LargeView>danielk/l/img2.jpg</LargeView>
<SmallView>danielk/s/img2.jpg</SmallView>
</Product>
</Products>
</Designer>

It is listing all products under each designer and reqeating the group of products once for each product that is in the list. The structure is correct and all else is working I just can't figure out how to change my looping structure or my query to get it to display correctly. My code is as follows:

<cfxml variable="myDoc">
<Products>
<cfloop query="getCategories">
<Category title="<cfoutput>#getCategories.txtTitle#</cfoutput>" label="<cfoutput>#getCategories.txtLabel#</cfoutput>">
<cfquery name="getDesigners" datasource="octannerXML">
SELECT txtTitle, txtWebsite, pk_designerID
FROM tblDesigners
INNER JOIN tblProducts
ON tblDesigners.pk_designerID = tblProducts.numDesignerID
WHERE tblProducts.numCatID = #getCategories.pk_catID# </cfquery>
<Designer>
<Title><cfoutput>#getDesigners.txtTitle#</cfoutput></Title>
<Products>
<cfloop query="getDesigners">
<cfquery name="getProducts" datasource="octannerXML">
SELECT *
FROM tblProducts
WHERE tblProducts.numCatID = #getCategories.pk_catID#
AND tblProducts.numDesignerID = #getDesigners.pk_designerID#
</cfquery>
<cfoutput query="getProducts">
<Product>
<LargeView>#getProducts.txtLargeView#</LargeView>
<SmallView>#getProducts.txtSmallView#</SmallView>
</Product>
</cfoutput>
</cfloop>
</Products>
<Website target="_blank">
<cfoutput>#getDesigners.txtWebsite#</cfoutput>
</Website>
</Designer>
</Category>
</cfloop>
</Products>
</cfxml>

Any Help is appriciated!

Thanks

Jason

[harsh789]

Try using distinctrows in following query.

SELECT DISTINCTROWS txtTitle, txtWebsite, pk_designerID
FROM tblDesigners
INNER JOIN tblProducts
ON tblDesigners.pk_designerID = tblProducts.numDesignerID
WHERE tblProducts.numCatID = #getCategories.pk_catID#