Should be something along the lines of: Select cat.name, sub.name, sec.name, prod.name from product prod inner join section sec on sec.sectionid = prod.sectionid inner join subcategory sub on sub.subcategoryid = prod.subcategoryid inner join category on cat.categoryid = prod.categoryid group by cat.name, sub.name, sec.name, prod.name order by cat.name
"Seona Bellamy" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > Hi guys, > > I'm stuck. I currently have a series of queries that work just fine to > output the required data, but I've just been asked to have it > alphabetically > ordered and I can't figure out how to do it. The setup is as follows: > > I have three tables: Category, Subcategory and Section (it's a hierarchy > in > that order). Then there is a Product table, and each product is has a > CategoryID, SubcategoryID and SectionID. I need to output the list of > products grouped by their hierarchy. > > eg: > Category Name > - Subcategory Name > - Section Name > - Product > - Product > - Section Name > - Product > - Product > - Subcategory Name > - Section Name > - Product > ...etc... > > So I currently have a query that gets the product details and does ORDER > BY > CategoryID, SubcategoryID, SectionID, ItemName. > > I use <cfoutput> with the group attribute to loop over the query and at > each > step I query the relevant table to get the name of the current Category, > Subcategory or Section based on the ID number. This works just fine, but > because it's ordered by ID and not by name, it's not alphabetical. > > I've looked up a bunch of information about table joins, which I'm sure is > the way I'm going to need to go, and get all the information with one > query > so I can sort by name, but I can't figure it out. Can anyone suggest a way > that I can get this data in such a way that I can order it by the names of > Categories, etc, instead of their ID numbers, and still use the group > thing > to cycle through all the products under each one? > > Or is there a better way of doing the whole thing under these > circumstances? > > Any suggestions would be greatly appreciated. > > Cheers, > > Seona. > > __________________________________________________________________ > << ella for Spam Control >> has removed Spam messages and set aside Later > for me > You can use it too - and it's FREE! http://www.ellaforspam.com > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.740 / Virus Database: 494 - Release Date: 16/08/2004 > > > > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/
