Outer join

From: Hagedorn, Linda <lindah_at_epocrates.com>
Date: Tue, 03 Jul 2001 17:24:06 -0700
Message-ID: <F001.00340CBD.20010703171038@fatcity.com>

I'm sure the answer is in front of me, but I don't see it......

Outer join on two tables, one with 1000+ survey rows, one with 11 description rows. Problem is the user wants to see all eleven description rows for all surveys, whether or not there are rows for the descriptions.

This works as expected:
SQL> select b.survey_id, substr(a.description,1,25), count(*)
2 from tracking.emailstatusdescriptions a ,tracking.emailstatus b
3 where a.description_id = b.description_id(+)
4 and b.survey_id(+) = 140
5 group by survey_id(+), a.description ;

SURVEY_ID SUBSTR(A.DESCRIPTION,1,25 COUNT(*)

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


       140 Logged In                          1

       140 Message Bounced                    1

       140 Message Sent                       5

       140 Survey Closed                      2

       140 Survey Completed                  34

       140 User Not Qualified                 3

           Login Failure                      1

           Pre Login                          1

           Send Failed                        1

User Clicked Through 1
User Opted Out of 1

11 rows selected.

When I remove the 'and b.survey_id(+) = 140' to include all surveys, the outer join fails to produce all 11 rows per survey_id. SQL> select b.survey_id, substr(a.description,1,25), count(*)
2 from tracking.emailstatusdescriptions a ,tracking.emailstatus b
3 where a.description_id = b.description_id(+)
4 group by b.survey_id(+), a.description ;

SURVEY_ID SUBSTR(A.DESCRIPTION,1,25 COUNT(*)

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


       120 Survey Completed                   2

       140 Logged In                          1

       140 Message Bounced                    1

       140 Message Sent                       5

       140 Survey Closed                      2

       140 Survey Completed                  34

       140 User Not Qualified                 3

       162 Message Bounced                    9

       162 Message Sent                     337

       162 Survey Closed                     87

       162 Survey Completed                  85

       162 User Clicked Through              12

       162 User Not Qualified                77

       162 User Opted Out of Honors           7

       182 Login Failure                     10

       182 Message Bounced                   55

       182 Message Sent                     981

       182 Pre Login                          2

       182 Survey Closed                     60

       182 Survey Completed                 173

       182 User Clicked Through              79

       182 User Not Qualified               380
       182 User Opted Out of Honors           9
<snip,snip>
47 rows selected.

SQL> desc tracking.emailstatus
Name                   Null?    Type
-------------------- -------- -------------------------------------------------
MESSAGE_ID           NOT NULL NUMBER
MSG_ID                        VARCHAR2(255)
USERID               NOT NULL NUMBER
SURVEY_ID            NOT NULL NUMBER
DESCRIPTION_ID       NOT NULL NUMBER
ERRORMSG                      VARCHAR2(4000)
CREATE_DT                     DATE
STATUSUPDATE                  DATE
CDESCRIPTION_ID               NUMBER
CERRORMSG                     VARCHAR2(4000)
CSENT_DT                      DATE
CSTATUSUPDATE                 DATE
SQL> desc tracking.emailstatusdescriptions

Name                   Null?    Type
-------------------- -------- --------------------------

DESCRIPTION_ID NOT NULL NUMBER
DESCRIPTION NOT NULL VARCHAR2(255)
SQL> select count(*) from tracking.emailstatusdescriptions ;

COUNT(*)

If this looks familiar and someone sees my error, please let me know.

Thank you and best regards,

Linda Hagedorn Received on Tue Jul 03 2001 - 19:24:06 CDT