CODEWITHSUNDEEP
sqloracle-database
cal0064
cal0064

Reputation: 13

Multiple left-outer join results in duplicated rows

I have a big table having Incidents. With this I want to attach values from Incident Attachment and Incident Comments. I have used Left outer join as there may or may not be any comments OR attachment for a Incident.
Now when I use multiple outer join i get duplicated rows.

There is a incidentID value that is common for all the tables

INC1   ATT1   COMMENT1
INC1   ATT1   COMMENT2
INC1   ATT2   COMMENT1
INC1   ATT2   COMMENT2

But actually the output should be:

INC1   ATT1   COMMENT1
INC1   ATT2   COMMENT2

so that the attachment and the comment values are independent when attaching themselves to the incident table.

select inc,att.attname,comment.commenttext 
from   inc 
       left outer join att inc.incidentID=att.incidentID
       left outer join comment inc.incidentID=comment .incidentID

Is this possible?

Upvotes: 1

Views: 1783

Answers (1)

Tebbe
Tebbe

Reputation: 1372

One of the things I'm taking away from your responses so far is that, for a given incident, there's no direct relationship between its attachments and its comments. If I understand that correctly, you want to treat an incident as a simple "container" for attachments and comments, and you just want a listing of each. Which attachment happens to appear in the same row as a particular comment is incidental to you.

TEST DATA:

SQL> CREATE TABLE inc (incidentid NUMBER, incname VARCHAR2(20));

Table created.

SQL> INSERT INTO inc VALUES (1,'incident 1');

1 row created.

SQL> INSERT INTO inc VALUES (2,'incident 2');

1 row created.

SQL> CREATE TABLE att (att_id NUMBER, incidentid NUMBER, attname VARCHAR2(20));

Table created.

SQL> INSERT INTO att VALUES (101, 1, 'attachment 1');

1 row created.

SQL> INSERT INTO att VALUES (102, 1, 'attachment 2');

1 row created.

SQL> INSERT INTO att VALUES (103, 1, 'attachment 3');

1 row created.

SQL> INSERT INTO att VALUES (104, 1, 'attachment 4');

1 row created.

SQL> INSERT INTO att VALUES (105, 1, 'attachment 5');

1 row created.

SQL> INSERT INTO att VALUES (110, 2, 'attachment A');

1 row created.

SQL> INSERT INTO att VALUES (111, 2, 'attachment B');

1 row created.

SQL> INSERT INTO att VALUES (112, 2, 'attachment C');

1 row created.

SQL> CREATE TABLE comments (comment_id NUMBER, incidentid NUMBER, commenttext VARCHAR2(20));

Table created.

SQL> INSERT INTO comments VALUES (201, 1, 'first comment');

1 row created.

SQL> INSERT INTO comments VALUES (202, 1, 'second comment');

1 row created.

SQL> INSERT INTO comments VALUES (203, 1, 'third comment');

1 row created.

PROPOSED QUERY:

SQL> WITH a AS (
  2      SELECT att.incidentid
  3      ,      COUNT(att.att_id) rows_per_incident
  4      FROM   att
  5      GROUP BY att.incidentid
  6      UNION ALL
  7      SELECT comments.incidentid
  8      ,      COUNT(comments.comment_id) rows_per_incident
  9      FROM   comments
 10      GROUP BY comments.incidentid
 11  )
 12  ,   b AS  (
 13      SELECT inc.incidentid
 14      ,      inc.incname
 15      ,      ROW_NUMBER()
 16             OVER (PARTITION BY inc.incidentid ORDER BY NULL) row_num
 17      FROM   inc
 18      ,     (SELECT ROWNUM multiplier FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(rows_per_incident) FROM a))
 19  )
 20  ,   c AS (
 21      SELECT  att.att_id
 22      ,       att.incidentid
 23      ,       att.attname
 24      ,       ROW_NUMBER()
 25              OVER (PARTITION BY att.incidentid ORDER BY att.att_id) att_rn
 26      FROM    att
 27  )
 28  ,   d AS (
 29      SELECT comments.comment_id
 30      ,      comments.incidentid
 31      ,      comments.commenttext
 32      ,      ROW_NUMBER()
 33             OVER (PARTITION BY comments.incidentid ORDER BY comments.comment_id) comm_rn
 34      FROM   comments
 35  )
 36  ,   e AS (
 37      SELECT  c.incidentid
 38      ,       c.att_id
 39      ,       c.attname
 40      ,       c.att_rn      rn
 41      ,       d.comment_id
 42      ,       d.commenttext
 43      FROM    c
 44      ,       d
 45      WHERE   c.incidentid = d.incidentid (+)
 46      AND     c.att_rn     = d.comm_rn (+)
 47      UNION ALL
 48      SELECT  TO_NUMBER(NULL) incidentid
 49      ,       TO_NUMBER(NULL) att_id
 50      ,       NULL            attname
 51      ,       d.comm_rn       rn
 52      ,       d.comment_id
 53      ,       d.commenttext
 54      FROM    d
 55      WHERE   NOT EXISTS (SELECT  NULL
 56                          FROM    c
 57                          WHERE   c.incidentid = d.incidentid
 58                          AND     c.att_rn     = d.comm_rn)
 59  )
 60  ,   f AS (
 61  SELECT  b.incidentid
 62  ,       b.incname
 63  ,       b.row_num
 64  ,       e.att_id
 65  ,       e.attname
 66  ,       e.comment_id
 67  ,       e.commenttext
 68  FROM    b
 69      LEFT OUTER JOIN e ON  b.incidentid = e.incidentid
 70                        AND b.row_num    = e.rn
 71  )
 72  SELECT  f.incidentid
 73  ,       f.incname
 74  ,       f.att_id
 75  ,       f.attname
 76  ,       f.comment_id
 77  ,       f.commenttext
 78  FROM    f
 79  WHERE   NOT (f.att_id IS NULL AND f.comment_id IS NULL)
 80  ORDER BY f.incidentid
 81  ,        f.row_num
 82  ;

INCIDENTID INCNAME                  ATT_ID ATTNAME              COMMENT_ID COMMENTTEXT
---------- -------------------- ---------- -------------------- ---------- --------------------
         1 incident 1                  101 attachment 1                201 first comment
         1 incident 1                  102 attachment 2                202 second comment
         1 incident 1                  103 attachment 3                203 third comment
         1 incident 1                  104 attachment 4
         1 incident 1                  105 attachment 5
         2 incident 2                  110 attachment A
         2 incident 2                  111 attachment B
         2 incident 2                  112 attachment C

8 rows selected.

SQL>

Upvotes: 2

Related Questions