Re: Tough (?) SELECT statement
Date: 30 Oct 2002 16:05:15 -0800
Message-ID: <6dae7e65.0210301605.4021a3b8_at_posting.google.com>
nchimera_at_yahoo.com (Chimera) wrote in message news:<7afde90.0210300724.461d5aab_at_posting.google.com>...
> 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'
>
I think youre better of returning null if it doesnt exist. I'll give two alternatives:
alt 1:
SELECT
HOME.ADDRESS1,
HOME.ADDRESS2,
(select ADDRESS1 FROM ADDRESS NAMEID=HOME.NAMEID and ADDRESS_TYPE='Mail') as M_ADRESS1,
(select ADDRESS2 FROM ADDRESS NAMEID=HOME.NAMEID and
ADDRESS_TYPE='Mail') as M_ADRESS2
FROM ADDRESS "HOME"
WHERE HOME.NAMEID=some_name_id AND HOME.ADDRESS_TYPE='Home'
alt 2:
SELECT HOME.ADDRESS1, HOME.ADDRESS2, MAIL.ADDRESS1,MAIL.ADDRESS2 FROM
ADDRESS "MAIL" right outer join ADDRESS "HOME" on
MAIL.NAMEID=HOME.NAMEID
WHERE HOME.NAMEID=some_name_id AND MAIL.ADDRESS_TYPE='Mail' AND
HOME.ADDRESS_TYPE='Home'
If you are insisting on returning homeadr instead of null ;-), have a look at the function COALESCE. In the 2 example you would use it as:
SELECT HOME.ADDRESS1, HOME.ADDRESS2,
coalesce(MAIL.ADDRESS1,HOME.ADDRESS1), coalesce(MAIL.ADDRESS2,
HOME.ADDRESS2) ...
HTH
/Lennart
Received on Thu Oct 31 2002 - 01:05:15 CET