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