CODEWITHSUNDEEP
sqloracle-databasejoin
Little_Tomato
Little_Tomato

Reputation: 25

Table self-join

I have a table_1 that looks like this:

Common_ID Type Person_ID
123 0 78
123 2 89
123 2 63
123 2 26
456 0 99
456 2 13

So Person 78 is related to Person 89, 63, 26, because they share the same Common_ID 123. Person 78 has Type 0 which means he is the Primary contact. Person 89, 63, 26 have Type 2 so they are Secondary contact.

I want to create a new table that looks like this:

Primary Other1 Other2 Other3
78 89 63 26
99 13 null null

I think I need to self-join this table. I write this:

select T1.person_ID,T2.person_ID, T3.person_ID, T4.person_ID,  
from table_1 T1
left outer join table_1 T2 on T1.Common_ID=T2.Common_ID
left outer join table_1 T3 on T1.Common_ID=T3.Common_ID
left outer join table_1 T4 on T1.Common_ID=T4.Common_ID
where T1.type=0
and T2.type=2
and T3.type=2
and T4.type=2
and T1.person_ID <> T2.person_ID
and T1.person_ID <> T3.person_ID
and T1.person_ID <> T4.person_ID
and T2.person_ID <> T3.person_ID
and T2.person_ID <> T4.person_ID
and T3.person_ID <> T4.person_ID

The reason I added filters like and T1.person_ID <> T2.person_ID is because I don't want the same Secondary person to appear multiple times.

However the code above doesn't return the desired result. Because of filters like and T1.person_ID <> T2.person_ID, person 99 and 13 don't get returned, which I think it's due to the fact that Oracle cannot compare NULL?

I do want all persons returned including person 99 and 13. Could you please help? Thank you very much!

Upvotes: 0

Views: 58

Answers (3)

d r
d r

Reputation: 7846

One of the options could be to join two queries from the same table:

WITH        --  Sample data 
    tbl (COMMON_ID, A_TYPE, PERSON_ID) AS
        (   
            Select 123, 0, 78 From Dual Union All
            Select 123, 2, 89 From Dual Union All
            Select 123, 2, 63 From Dual Union All
            Select 123, 2, 26 From Dual Union All
            Select 456, 0, 99 From Dual Union All
            Select 456, 2, 13 From Dual 
        )

Main SQL

SELECT  t.PRIMARY, 
        Max(CASE RN WHEN 1 THEN PERSON_ID END) "OTHER_1",
        Max(CASE RN WHEN 2 THEN PERSON_ID END) "OTHER_2",
        Max(CASE RN WHEN 3 THEN PERSON_ID END) "OTHER_3"
FROM    ( Select  COMMON_ID, 
                  Max(CASE WHEN A_TYPE = 0 THEN PERSON_ID END) "PRIMARY"
          From    tbl
          Group By COMMON_ID
          Order By COMMON_ID  ) t
INNER JOIN  ( Select  ROW_NUMBER() OVER(Partition By COMMON_ID Order By PERSON_ID DESC) "RN", 
                      COMMON_ID, PERSON_ID
              From    tbl
              Where   A_TYPE = 2
            ) t2 ON(t2.COMMON_ID = t.COMMON_ID)
GROUP BY t.PRIMARY

R e s u l t :

PRIMARY OTHER_1 OTHER_2 OTHER_3
78 89 63 26
99 13 Null Null

Note: If there are more than 3 other person ids you should handle that too either putting them as a list in one column or the same way as in this answer. I ordered the others putting the newest person ID first as in your expected result.

Upvotes: 0

MT0
MT0

Reputation: 168505

You can use the ROW_NUMBER analytic function and then PIVOT (and can do it without any self-joins):

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY common_id, type ORDER BY person_id) AS rn
  FROM   table_name t
)
PIVOT (
  MAX(person_id)
  FOR (type, rn) IN (
    (0, 1) AS primary,
    (2, 1) AS other1,
    (2, 2) AS other2,
    (2, 3) AS other3
  )
)

Which, for the sample data:

CREATE TABLE table_name (Common_ID, Type, Person_ID) AS
SELECT 123, 0, 78 FROM DUAL UNION ALL
SELECT 123, 2, 89 FROM DUAL UNION ALL
SELECT 123, 2, 63 FROM DUAL UNION ALL
SELECT 123, 2, 26 FROM DUAL UNION ALL
SELECT 456, 0, 99 FROM DUAL UNION ALL
SELECT 456, 2, 13 FROM DUAL;

Outputs:

COMMON_ID PRIMARY OTHER1 OTHER2 OTHER3
123 78 26 63 89
456 99 13 null null

fiddle

Upvotes: 0

Richard Hubley
Richard Hubley

Reputation: 2320

You can add the clauses to the join, so that you don't exclude the nulls from full query

select T1.person_ID,T2.person_ID, T3.person_ID, T4.person_ID,  
from table_1 T1
left outer join table_1 T2 on T1.Common_ID=T2.Common_ID 
and T1.person_ID <> T2.person_ID
left outer join table_1 T3 on T1.Common_ID=T3.Common_ID
and T1.person_ID <> T3.person_ID
and T2.person_ID <> T3.person_ID
left outer join table_1 T4 on T1.Common_ID=T4.Common_ID
and T1.person_ID <> T4.person_ID
and T2.person_ID <> T4.person_ID
and T3.person_ID <> T4.person_ID
where T1.type=0
and T2.type=2
and T3.type=2
and T4.type=2

Upvotes: 0

Related Questions