Question on DISTINCT in a looped query

[goldensunrise]

Why does this not work?

<CFLOOP INDEX=&quot;Loop&quot; FROM=&quot;1&quot; TO=&quot;9&quot;>
<CFQUERY NAME=&quot;Loop&quot; DATASOURCE=&quot;Project&quot;>
SELECT DISTINCT Catagory.CatagoryID, Catagory.Catagory, SiteListing.*
FROM SiteListing, Catagory
WHERE (SiteListing.CatagoryID = #Loop#) AND Catagory.CatagoryID = SiteListing.CatagoryID
</CFQUERY>
<CFOUTPUT QUERY=&quot;Loop&quot;>
#Catagory#
(#Loop.RecordCount#)<BR>
</CFOUTPUT>
</CFLOOP>

 

[DarkMan]

What are you trying to accomplish???? DarkMan

 

[iza]

this is really weird :
&quot;WHERE (SiteListing.CatagoryID = #Loop#) AND Catagory.CatagoryID = SiteListing.CatagoryID&quot;

if you wanted all the catagories
WHERE Catagory.CatagoryID = SiteListing.CatagoryID

if you wanted only the 9 first catagories
WHERE SiteListing.CatagoryID < 10 AND Catagory.CatagoryID = SiteListing.CatagoryID

 

[DarkMan]

Well... I'd definitely say you wouldn't want to loop over a query nine times if there's another solution to get the same results in one pass...

From what I can tell, it looks like you probably want something like:

<CFQUERY NAME=&quot;Loop&quot; DATASOURCE=&quot;Project&quot;>
  SELECT DISTINCT 
    Catagory.CatagoryID, 
    Catagory.Catagory, 
    SiteListing.*
  FROM SiteListing, Catagory
  WHERE (SiteListing.CatagoryID > 10) AND
    Catagory.CatagoryID = SiteListing.CatagoryID
</CFQUERY>

<CFOUTPUT QUERY=&quot;Loop&quot;>
  #Catagory#
  (#CurrentRow#)<BR>
</CFOUTPUT>

Hope this helps... DarkMan

 

[iza]

yes i guess that's what goldensunrise wanted to do
except that the >10 is actually <10 but who cares !

 

[DarkMan]

WHOOPS!!! Good eye, iza DarkMan

 

[goldensunrise]

Thanks Guys. I got it figured out.