Use a self join something like : select a.CategoryName,b.parentname from categorytable a left outer join categorytable b on categoryid=ParentCategoryID -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 23 July 2001 08:03 To: CF-Talk Subject: OT: SQL for parent/child Seems like this should be easy, but I can't think how to construct a query that will grab the name of a parent category in the following situation... My category table consists of: CategoryID CategoryName ParentCategoryID If a category has a parent, ParentCategoryID carries the ID of the parent. If it doesn't, ParentCategory = 0. I'm trying to create a single query listing all the categories, and ALSO including a query column called ParentCategoryName, containing the name of the parent if that category has one. Could do it easily if the info were in 2 tables, but not sure how when all the info is in 1 table... Gene Kraybill ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
