SQL in cfquery 1

[doyle9732]

<CFQUERY NAME="GetSite" DATASOURCE="#DSN#">
SELECT ID, URL, PageName, CategoryID, Description, DateAdded, addedBy, Notes, SubcategoryID,(SELECT Category FROM CategoryList WHERE CategoryList.CategoryID=Webs.CategoryID) as CatName
FROM Webs
ORDER BY CategoryID, PageName
</CFQUERY>

I need to sort the list by CatName, but get an error if I change the ORDER BY to

ORDER BY CatName, PageName

The categoryID in the Webs table is just numbers, so they display in groups but not alphabetically.

Any idea on how to capture this information?

thanks!
Stephanie

 

[r937]

select W.ID
     , W.URL
     , W.PageName
     , W.CategoryID
     , W.Description
     , W.DateAdded
     , W.addedBy
     , W.Notes
     , W.SubcategoryID
     , CL.Category  as CatName
  from Webs as W
inner
  join CategoryList as CL
    on W.CategoryID
     = CL.CategoryID
order 
    by CL.Category
     , W.PageName

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[doyle9732]

thanks! You're a superstar!

Stephanie