3 Way left outer join - is it possible?

Debbie Sams

3 Way left outer join - is it possible?
We have 3 tables that I would like to join - directory, address and phone.
I want a left outer join on the directory and would prefer one table scan
and one query if possible

The following works for addresses:

SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(ADDR_DELIVERY_AD,'**N/A**'),
COALESCE(ADDR_CITY_NM,'**N/A**'),
COALESCE(ADDR_STATE_CD,'**N/A**'),
COALESCE(ADDR_ZIP_CD,'**N/A**'),
COALESCE(ADDR_ZIP_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_ADDRESS
ON DIR_ID_NO = ADDR_ID_NO;
====================
and this works for phones:

SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(PH_PHONE_NO,'**N/A**'),
COALESCE(PH_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_PHONE
ON DIR_ID_NO = PH_ID_NO;
===================
I'm trying to do somthing like below but can't get
the syntax to work, and don't even know if it's possible

SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(ADDR_DELIVERY_AD,'**N/A**'),
COALESCE(ADDR_CITY_NM,'**N/A**'),
COALESCE(ADDR_STATE_CD,'**N/A**'),
COALESCE(ADDR_ZIP_CD,'**N/A**'),
COALESCE(ADDR_ZIP_EXT_CD,'**N/A**'),
COALESCE(PH_PHONE_NO,'**N/A**'),
COALESCE(PH_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_PHONE AND
BADBADM.AA_ADDRESS
ON DIR_ID_NO = PH_ID_NO AND
DIR_ID_NO = ADDR_ID_NO;
===================

Thanks,

Debbie Sams Coordinator, Computer
Applications
University of Florida Information Systems
(352) 392-1285, Fax: (352) 392-6635 33 Tigert Hall
Email: [login to unmask email] P.O. Box 113275
Gainesville, FL. 32611



Dave Nance

Re: 3 Way left outer join - is it possible?
(in response to Debbie Sams)
Debbie,
Try something like the following:
SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(PH_PHONE_NO,'**N/A**'),
COALESCE(PH_EXT_CD,'**N/A**')
COALESCE(ADDR_DELIVERY_AD,'**N/A**'),
COALESCE(ADDR_CITY_NM,'**N/A**'),
COALESCE(ADDR_STATE_CD,'**N/A**'),
COALESCE(ADDR_ZIP_CD,'**N/A**'),
COALESCE(ADDR_ZIP_EXT_CD,'**N/A**')
FROM BADBADM.AA_DIRECTORY
LEFT OUTER JOIN BADBADM.AA_ADDRESS
ON DIR_ID_NO = ADDR_ID_NO
LEFT OUTER JOIN BADBADM.AA_PHONE
ON DIR_ID_NO = PH_ID_NO;

Do you really need the left outer join? Will a name not always have a phone and address?

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 12/19/00 07:45AM >>>
We have 3 tables that I would like to join - directory, address and phone.
I want a left outer join on the directory and would prefer one table scan
and one query if possible

The following works for addresses:

SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(ADDR_DELIVERY_AD,'**N/A**'),
COALESCE(ADDR_CITY_NM,'**N/A**'),
COALESCE(ADDR_STATE_CD,'**N/A**'),
COALESCE(ADDR_ZIP_CD,'**N/A**'),
COALESCE(ADDR_ZIP_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_ADDRESS
ON DIR_ID_NO = ADDR_ID_NO;
====================
and this works for phones:

SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(PH_PHONE_NO,'**N/A**'),
COALESCE(PH_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_PHONE
ON DIR_ID_NO = PH_ID_NO;
===================
I'm trying to do somthing like below but can't get
the syntax to work, and don't even know if it's possible

SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(ADDR_DELIVERY_AD,'**N/A**'),
COALESCE(ADDR_CITY_NM,'**N/A**'),
COALESCE(ADDR_STATE_CD,'**N/A**'),
COALESCE(ADDR_ZIP_CD,'**N/A**'),
COALESCE(ADDR_ZIP_EXT_CD,'**N/A**'),
COALESCE(PH_PHONE_NO,'**N/A**'),
COALESCE(PH_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_PHONE AND
BADBADM.AA_ADDRESS
ON DIR_ID_NO = PH_ID_NO AND
DIR_ID_NO = ADDR_ID_NO;
===================

Thanks,

Debbie Sams Coordinator, Computer
Applications
University of Florida Information Systems
(352) 392-1285, Fax: (352) 392-6635 33 Tigert Hall
Email: [login to unmask email] P.O. Box 113275
Gainesville, FL. 32611






[login to unmask email]

Re: 3 Way left outer join - is it possible?
(in response to Dave Nance)
This should work

SELECT COALESCE(DIR_ID_NO,'N/A'),
COALESCE(DIR_NAME, 'N/A'),
COALESCE(ADDR_DELIVERY_AD,'**N/A**'),
COALESCE(ADDR_CITY_NM,'**N/A**'),
COALESCE(ADDR_STATE_CD,'**N/A**'),
COALESCE(ADDR_ZIP_CD,'**N/A**'),
COALESCE(ADDR_ZIP_EXT_CD,'**N/A**'),
COALESCE(PH_PHONE_NO,'**N/A**'),
COALESCE(PH_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_PHONE PH ON
X.DIR_ID_NO = PH.PH_ID_NO
LEFT OUTER JOIN BADBADM.AA_ADDRESS AD ON
X.DIR_ID_NO = AD.ADDR_ID_NO;





"Sams, Debbie" <[login to unmask email]>@RYCI.COM> on 12/19/2000 07:45:48 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:

Subject: 3 Way left outer join - is it possible?


We have 3 tables that I would like to join - directory, address and phone.
I want a left outer join on the directory and would prefer one table scan
and one query if possible

The following works for addresses:

SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(ADDR_DELIVERY_AD,'**N/A**'),
COALESCE(ADDR_CITY_NM,'**N/A**'),
COALESCE(ADDR_STATE_CD,'**N/A**'),
COALESCE(ADDR_ZIP_CD,'**N/A**'),
COALESCE(ADDR_ZIP_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_ADDRESS
ON DIR_ID_NO = ADDR_ID_NO;
====================
and this works for phones:

SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(PH_PHONE_NO,'**N/A**'),
COALESCE(PH_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_PHONE
ON DIR_ID_NO = PH_ID_NO;
===================
I'm trying to do somthing like below but can't get
the syntax to work, and don't even know if it's possible

SELECT DIR_ID_NO,
DIR_NAME,
COALESCE(ADDR_DELIVERY_AD,'**N/A**'),
COALESCE(ADDR_CITY_NM,'**N/A**'),
COALESCE(ADDR_STATE_CD,'**N/A**'),
COALESCE(ADDR_ZIP_CD,'**N/A**'),
COALESCE(ADDR_ZIP_EXT_CD,'**N/A**'),
COALESCE(PH_PHONE_NO,'**N/A**'),
COALESCE(PH_EXT_CD,'**N/A**')
FROM (SELECT DIR_ID_NO, DIR_NAME
FROM BADBADM.AA_DIRECTORY) AS X
LEFT OUTER JOIN BADBADM.AA_PHONE AND
BADBADM.AA_ADDRESS
ON DIR_ID_NO = PH_ID_NO AND
DIR_ID_NO = ADDR_ID_NO;
===================

Thanks,

Debbie Sams Coordinator, Computer
Applications
University of Florida Information Systems
(352) 392-1285, Fax: (352) 392-6635 33 Tigert Hall
Email: [login to unmask email] P.O. Box 113275
Gainesville, FL. 32611