SQL Solution Desired

From: PMG <pete_g_at_2xtreme.net>
Date: Fri, 26 Feb 1999 06:04:58 GMT
Message-ID: <36D63992.4119CEEB@2xtreme.net>

I have a problem coming up with the right SQL for the following situation:

I have three tables as follows:

A). SALES_TERR

---

Name                            Null?    Type


------------------------------- -------- ----


TERR_CODE                       NOT NULL VARCHAR2(5)
TERR_DESCRIPTION                         VARCHAR2(30)
REPORTS_TO                               VARCHAR2(5)
ALIGNMENT_NO                    NOT NULL NUMBER

B). SALES_FORCE

---

Name                            Null?    Type


------------------------------- -------- ----


FORCE_ID                        NOT NULL NUMBER
FORCE_LNAME                              VARCHAR2(30)
FORCE_FNAME                              VARCHAR2(20)

C) SALES_FORCE_ALIGNMENTS

---

Name                            Null?    Type


------------------------------- -------- ----


TERR_CODE                       NOT NULL VARCHAR2(5)
ALIGNMENT_NO                    NOT NULL NUMBER
FORCE_ID                        NOT NULL NUMBER

I am trying to create a query which will list each Territory, Rep Name, Region, Region Code, and Region Manager's Name. The following query does what I want, with one exception. It does not show territories where the region manager position is open, i.e., there is no entry in SALES_FORCE_ALIGNMENTS. The problem seems to be centered on trying to do an outer join on tables A and B, and an outer join on tables B and C.

Any suggestions?

SELECT st.terr_code          TERRITORY,
      sf.force_fname || ' '
         || sf.force_lname     REP,
    sf.force_title      TITLE,
      st.terr_description     TERR_NAME,
        st.reports_to         REGION,

    st1.terr_description REGION_NAME,     sf1.force_fname || ' '

         || sf1.force_lname RM
 FROM sales_terr st,

      sales_force sf,

    sales_force_alignments sfa,
    sales_terr st1,
    sales_force_alignments sfa1,
    sales_force sf1

 WHERE st.alignment_no = 1
 AND st.terr_code = sfa.terr_code(+)
 AND st.alignment_no = sfa.alignment_no(+)  AND sfa.force_id = sf.force_id(+)
 AND st1.terr_code = st.reports_to
 AND st.alignment_no = st1.alignment_no(+)  AND st1.terr_code = sfa1.terr_code(+)  AND sfa1.alignment_no = 1
 AND sfa1.force_id = sf1.force_id(+) Received on Fri Feb 26 1999 - 00:04:58 CST