Oracle Select Query For One To Many Relationship Between Two Tables

I am not a SQL expert but I am learning. I am trying to figure out how to write the query for two table in a one to many relationship. Table1 has the person information : (PersonId

Solution 1:

One method uses conditional aggregation:

select t1.PERSON_ID, t1.FIRST_NAME, t1.MIDDLE_NAME, t1.LAST_NAME, 
       max(casewhen t2.phone_type ='BUSINESS'then t2.PHONE_NUMBER end) as business,
       max(casewhen t2.phone_type ='PERSONAL'then t2.PHONE_NUMBER end) as personal,
       max(casewhen t2.phone_type ='HOME'then t2.PHONE_NUMBER end) as home
from Table1 t1 innerjoin
     Table2 t2
     on t2.PERSON_ID = t1.PERSON_ID
where t2.PHONE_TYPE in ('BUSINESS', 'PERSONAL', 'HOME') 
groupby t1.PERSON_ID, t1.FIRST_NAME, t1.MIDDLE_NAME, t1.LAST_NAME;

Solution 2:

As of Oracle 11g this option becomes availiable: WITH Table1 AS ( SELECT 1 PERSON_ID, 'John' FIRST_NAME, 'Carter' MIDDLE_NAME, 'Jones' LAST_NAME FROM DUAL ), Table2 AS ( SELECT 1 PERSON_ID, '111-111-1111' PHONE_NUMBER, 'HOME' PHONE_TYPE FROM DUAL UNION ALL SELECT 1 PERSON_ID, '111-111-1112' PHONE_NUMBER, 'PERSONAL' PHONE_TYPE FROM DUAL UNION ALL SELECT 1 PERSON_ID, '111-111-1113' PHONE_NUMBER, 'BUSINESS' PHONE_TYPE FROM DUAL ) SELECT * FROM ( SELECT T1.PERSON_ID, T1.FIRST_NAME, T1.MIDDLE_NAME, T1.LAST_NAME, T2.PHONE_TYPE, t2.PHONE_NUMBER FROM Table1 T1 INNER JOIN Table2 t2 ON t2.PERSON_ID = T1.PERSON_ID WHERE t2.PHONE_TYPE IN ('HOME', 'PERSONAL', 'BUSINESS') ) PIVOT ( MAX(PHONE_NUMBER) AS Tel FOR (PHONE_TYPE) IN ('HOME' AS HOME, 'PERSONAL' AS PERSONAL, 'BUSINESS' AS BUSINESS) )