You need to refer to the pages table twice (once for children c and once for parents p)
SELECT c.pageID, c.fulltitle, p.fulltitle AS parenttitle FROM pages c, pages p WHERE c.parentID = p.pageID > The table, called 'pages', has the following basic fields: > > pageID (PK) > fullTitle > parentID (FK - pageID of parent page or 0 if no parent) > > I want one query to get the IDs, titles, and title of > parent if there is one. I thought this would work: > > SELECT pageID, fullTitle, > (SELECT fullTitle > FROM pages > WHERE parentID = pageID) AS parentTitle > FROM pages > > But apparently not. Is a subquery the way to do it? > I can think of a convoluted method that involves > looping through a basic query getting all page info, > and querying again for each record, appending > parentTitle if there is one, but I know there must > be a way to get it all done in one fell swoop! > > thanks, > > - Gyrus > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > - [EMAIL PROTECTED] > work: http://www.tengai.co.uk > play: http://www.norlonto.net > - PGP key available > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
