Re: Outer join

From: Regina Harter <rharter_at_emc-inc.com>
Date: Tue, 03 Jul 2001 18:30:44 -0700
Message-ID: <F001.00340D01.20010703182524@fatcity.com>

What you did was tell it to do an outer join for any description that didn't exist in the table, not for any description that didn't exist in the survey id group.  Did I explain that well?  In order to do what you want you will need to do something like:

select a.survey_id, b.description, count(*) from (select distinct d.survey_id, e.description_id from estatdesc e, estat d) c
estatdesc b, estat a
where a.survey_id = c.survey_id

and b.description_id = c.description_id
and a.description_id (+) = b.description_id
group by a.survey_id, b.description

I haven't tested this and have no idea if this will work at all, but do you see what I'm going for?  You need some sort of intermediate table that will provide a list of all descriptions attached to each survey id.

At 05:10 PM 7/3/01 -0800, you wrote:

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(*)

---

       

11

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 - 20:30:44 CDT