I posted recently about how to define a relationship in Access where the PK for a FK field is actually in the same table. I've sorted that, but getting the query right is another matter...
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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc 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