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
