Tough (?) SELECT statement

I'd like some help composing a select statment to perform the following:

I have an ADDRESS table with the standard ADDRESS1, AADDRESS2, etc columns as well as ADDRESS_TYPE and NAMEID columns. A person (NAMEID) can have multiple entries (rows), differentiated by the ADDRESS_TYPE value- 'Home', 'Work', 'Mail', etc.
To simplify, the 'Home' address always exists, but the others may not. The mental block I am having is to be able to select (in one row) both the 'Home' and 'Mail' addresses- but if the 'Mail' address does not exist then retrun the 'Home' address in it's place. SOmething like:

SELECT HOME.ADDRESS1, HOME.ADDRESS2, MAIL.ADDRESS1,MAIL.ADDRESS2 FROM ADDRESS "MAIL", ADDRESS "HOME"
WHERE MAIL.NAMEID=some_name_id AND HOME.NAMEID=some_name_id AND MAIL.ADDRESS_TYPE='Mail' AND HOME.ADDRESS_TYPE='Home'

the problem is that if ther is no Mail address, nothing is returned for that NAMEID. I've tried a bunch of things (subquery with an NVL(), decode().. ) but I'm stumped- maybe it can't be done with one statement (or maybe this is trivial for someone out there) ?

Thanks. Received on Wed Oct 30 2002 - 16:24:58 CET