How to output data on one line per ID in Oracle SQL?

Question

This works to get me all the data I need, but outputs a separate row for each side of the UNION when what I really want is to get all parent contacts on the same row as the student ID. That is, I want a row with a.id and then all the info for contact_1, contact_2, contact_3 and contact_4 in the same row. I'm guessing UNION was the wrong choice...

SELECT final.* 
    FROM (
        SELECT 
        --Name 1 in P1 household
        a.id
        ,a.name1_web_user_id as contact_1_id
        ,a.name1_full_name as contact_1_name
        ,case 'Name1:Mobile' 
            when Other_No_Type_1 then Other_No_1
            when Other_No_Type_2 then Other_No_2
            when Other_No_Type_3 then Other_No_3
            when Other_No_Type_4 then Other_No_4
            end as contact_1_mobile
        ,a.email as contact_1_email
       
        --Name 2 in P1 household
        ,a.name2_web_user_id as contact_2_id
        ,a.name2_full_name as contact_2_name
        ,case 'Name2:Mobile' 
            when Other_No_Type_1 then Other_No_1
            when Other_No_Type_2 then Other_No_2
            when Other_No_Type_3 then Other_No_3
            when Other_No_Type_4 then Other_No_4
            end as contact_2_mobile
        ,a.EMAIL_2 as contact_2_email
            
    FROM rg_student s left outer join rg_addr a on s.id = a.id
                WHERE  (
                (a.addr_code='P1' AND a.rg_active = 'Y') AND ((a.name1_web_user_id is not null) OR (a.name2_web_user_id is not null))
                            AND a.id in(SELECT id from rg_student where student_group='Student')
                        )
    UNION
    
        SELECT 
        --Name 1 in P2 household
        a.id
        ,a.name1_web_user_id as contact_3_id
        ,a.name1_full_name as contact_3_name
        ,case 'Name1:Mobile' 
            when Other_No_Type_1 then Other_No_1
            when Other_No_Type_2 then Other_No_2
            when Other_No_Type_3 then Other_No_3
            when Other_No_Type_4 then Other_No_4
            end as contact_3_mobile
        ,a.email as contact_3_email
        
        --Name 2 in P2 household
        ,a.name2_web_user_id as contact_4_id
        ,a.name2_full_name as contact_4_name
        ,case 'Name2:Mobile' 
            when Other_No_Type_1 then Other_No_1
            when Other_No_Type_2 then Other_No_2
            when Other_No_Type_3 then Other_No_3
            when Other_No_Type_4 then Other_No_4
            end as contact_4_mobile
        ,a.EMAIL_2 as contact_4_email
            
    FROM rg_student s left outer join rg_addr a on s.id = a.id
                WHERE  (
                        (a.addr_code='P2' AND a.rg_active = 'Y') AND ((a.name1_web_user_id is not null) OR (a.name2_web_user_id is not null))
                            AND a.id in(SELECT id from rg_student where student_group='Student')
                        )                
        )final
    ORDER BY final.id

I've tried to replace the UNION with JOIN, as guided, and simplified the select as follows, but now get error about FROM being in the wrong place.


        SELECT 
        --Name 1 in P1 household
        s.id
        ,a.name1_web_user_id as contact_1_id
        ,a.name1_full_name as contact_1_name
        ,case 'Name1:Mobile' 
            when a.Other_No_Type_1 then Other_No_1
            when a.Other_No_Type_2 then Other_No_2
            when a.Other_No_Type_3 then Other_No_3
            when a.Other_No_Type_4 then Other_No_4
            end as contact_1_mobile
        ,a.email as contact_1_email
       
        --Name 2 in P1 household
        ,a.name2_web_user_id as contact_2_id
        ,a.name2_full_name as contact_2_name
        ,case 'Name2:Mobile' 
            when a.Other_No_Type_1 then Other_No_1
            when a.Other_No_Type_2 then Other_No_2
            when a.Other_No_Type_3 then Other_No_3
            when a.Other_No_Type_4 then Other_No_4
            end as contact_2_mobile
        ,a.email_2 as contact_2_email
            
        --Name 1 in P2 household
        s.id
        ,b.name1_web_user_id as contact_3_id
        ,b.name1_full_name as contact_3_name
        ,case 'Name1:Mobile' 
            when b.Other_No_Type_1 then Other_No_1
            when b.Other_No_Type_2 then Other_No_2
            when b.Other_No_Type_3 then Other_No_3
            when b.Other_No_Type_4 then Other_No_4
            end as contact_3_mobile
        ,b.email as contact_3_email
        
        --Name 2 in P2 household
        ,b.name2_web_user_id as contact_4_id
        ,b.name2_full_name as contact_4_name
        ,case 'Name2:Mobile' 
            when b.Other_No_Type_1 then Other_No_1
            when b.Other_No_Type_2 then Other_No_2
            when b.Other_No_Type_3 then Other_No_3
            when b.Other_No_Type_4 then Other_No_4
            end as contact_4_mobile
        ,b.email_2 as contact_4_email
            
    FROM rg_student s
        left outer join rg_addr a on s.id=a.id
        WHERE  ((a.addr_code='P1' AND a.rg_active = 'Y') 
                AND s.id in(SELECT id from rg_student where student_group='Student'))
        left outer join rg_addr b on s.id=b.id
        WHERE  ((a.addr_code='P2' AND b.rg_active = 'Y') 
                 AND s.id in(SELECT id from rg_student where student_group='Student'))

This now gives me the single row per student that I need, but also disregards my need to (SELECT id from rg_student where student_group='Student') in the FROM, so it's now returning all students in any group (incl. withdrawn, graduate, etc.). I hear that I can't use WHERE in the FROM, but not sure how to define the student_group I need.

       SELECT DISTINCT
       --Name 1 in P1 household
       s.id
       ,a.name1_web_user_id as contact_1_id
       ,a.name1_full_name as contact_1_name
       ,case 'Name1:Mobile' 
           when a.Other_No_Type_1 then a.Other_No_1
           when a.Other_No_Type_2 then a.Other_No_2
           when a.Other_No_Type_3 then a.Other_No_3
           when a.Other_No_Type_4 then a.Other_No_4
           end as contact_1_mobile
       ,a.email as contact_1_email
      
       --Name 2 in P1 household
       ,a.name2_web_user_id as contact_2_id
       ,a.name2_full_name as contact_2_name
       ,case 'Name2:Mobile' 
           when a.Other_No_Type_1 then a.Other_No_1
           when a.Other_No_Type_2 then a.Other_No_2
           when a.Other_No_Type_3 then a.Other_No_3
           when a.Other_No_Type_4 then a.Other_No_4
           end as contact_2_mobile
       ,a.email_2 as contact_2_email
           
       --Name 1 in P2 household
       ,s.id
       ,b.name1_web_user_id as contact_3_id
       ,b.name1_full_name as contact_3_name
       ,case 'Name1:Mobile' 
           when b.Other_No_Type_1 then b.Other_No_1
           when b.Other_No_Type_2 then b.Other_No_2
           when b.Other_No_Type_3 then b.Other_No_3
           when b.Other_No_Type_4 then b.Other_No_4
           end as contact_3_mobile
       ,b.email as contact_3_email
       
       --Name 2 in P2 household
       ,b.name2_web_user_id as contact_4_id
       ,b.name2_full_name as contact_4_name
       ,case 'Name2:Mobile' 
           when b.Other_No_Type_1 then b.Other_No_1
           when b.Other_No_Type_2 then b.Other_No_2
           when b.Other_No_Type_3 then b.Other_No_3
           when b.Other_No_Type_4 then b.Other_No_4
           end as contact_4_mobile
       ,b.email_2 as contact_4_email
           
   FROM rg_student s
       left outer join rg_addr a on s.id=a.id
       AND  a.addr_code='P1' AND a.rg_active = 'Y' 
       AND s.id in(SELECT id from rg_student where student_group='Student')
           left outer join rg_addr b on s.id=b.id
           AND  a.addr_code='P2' AND b.rg_active = 'Y' 
           AND s.id in(SELECT id from rg_student where student_group='Student')

Junjie · Accepted Answer

Yes, union creates other rows for each student's id.

To put all the contact info on one row per id, you have to join your rg_addr to rg_student twice, with the difference in the condition for addr_code, like so:

select s.id,
  a.name1_web_user_id contact_1_id,
  a.name1_full_name contact_1_name,
  a.email contact_1_email,
  ... --other info of name 1 in P1 household
  a.name2_web_user_id contact_2_id,
  a.name2_full_name contact_2_name,
  a.email_2 contact_2_email,
  ... --other info of name 2 in P1 household
  b.name1_web_user_id contact_3_id,
  b.name1_full_name contact_3_name,
  b.email contact_3_email,
  ... --other info of name 1 in P2 household
  b.email_2 contact_4_email,
  b.name2_web_user_id contact_4_id,
  b.email_2 contact_4_email,
  ... --other info of name 2 in P2 household
from rg_student s
  left outer join rg_addr a on s.id=a.id
    and a.addr_code='P1' and a.rg_active='Y'
    and ... --and other required conditions
  left outer join rg_addr b on s.id=b.id
    and a.addr_code='P2' and b.rg_active='Y'
    and ... --and other required conditions

Note that what I put for id is s prefix instead of a to make sure there's a student ID in case the student does not have corresponding rg_addr record.

Edit: Additional code changes for student group requirement:

...
FROM rg_student s
  left outer join rg_addr a on s.id=a.id
    AND  a.addr_code='P1' AND a.rg_active = 'Y' 
    --AND s.id in(SELECT id from rg_student where student_group='Student')
  left outer join rg_addr b on s.id=b.id
    AND  a.addr_code='P2' AND b.rg_active = 'Y' 
    --AND s.id in(SELECT id from rg_student where student_group='Student')
WHERE s.student_group='Student'

How to output data on one line per ID in Oracle SQL?

Answers (1)

Related Questions