John John - 4 months ago 25
SQL Question

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, FirstName, MiddleName, LastName)
and
table2
has
(PersonId, Phone, PhoneType)
.

Here is my query so far

select Table1.PERSON_ID,
Table1.FIRST_NAME,
Table1.MIDDLE_NAME,
Table1.LAST_NAME,
Table2.PHONE_NUMBER
from Table1
inner join Table2
on Table2.PERSON_ID = Table1.PERSON_ID
where Table2.PHONE_TYPE in ('BUSINESS','PERSONAL','HOME')


Here is a tables

Table1
PERSON_ID FIRST_NAME MIDDLE_NAME LAST_NAME
1 John Carter Jones

Table2
PERSON_ID PHONE_NUMBER PHONE_TYPE
1 111-111-1111 HOME
1 111-111-1112 PERSONAL
1 111-111-1113 BUSINESS


From my query I get

1 John Carter Jones 111-111-1111
1 John Carter Jones 111-111-1112
1 John Carter Jones 111-111-1113


I would like to achieve the following result

1 John Carter Jones 111-111-1111 111-111-1112 111-111-1113


I tried using LISTAGG to combine the phone numbers. It gives me all 3 phone numbers in one column. How can I get the output of the LISTAGG as 3 separate columns.

The output on TOAD looks like this

1 John M. Doe "2022222222, 2023333333"

Here is my query

select
PERSON.PERSON_ID,
PERSON.FIRST_NAME,
PERSON.MIDDLE_NAME,
PERSON.LAST_NAME,
LISTAGG(PHONE_NUMBER, ',') WITHIN GROUP (ORDER BY PHONENUMBERS.PHONE_NUMBER)
FROM Table1 PERSON
INNER JOIN (SELECT PERSON_ID, PHONE_NUMBER, UPDATED_DT, PHONE_TP_SHORT_DESC FROM Table2) PHONENUMBERS
ON PERSON.PERSON_ID = PHONENUMBERS.PERSON_ID AND PHONENUMBERS.PHONE_TP_SHORT_DESC IN ('HOME','BUSINESS','CELL')
GROUP BY
PERSON.PERSON_ID,
PERSON.FIRST_NAME,
PERSON.MIDDLE_NAME,
PERSON.LAST_NAME

Answer

One method uses conditional aggregation:

select t1.PERSON_ID, t1.FIRST_NAME, t1.MIDDLE_NAME, t1.LAST_NAME, 
       max(case when t2.phone_type = 'BUSINESS' then t2.PHONE_NUMBER end) as business,
       max(case when t2.phone_type = 'PERSONAL' then t2.PHONE_NUMBER end) as personal,
       max(case when t2.phone_type = 'HOME' then t2.PHONE_NUMBER end) as home
from Table1 t1 inner join
     Table2 t2
     on t2.PERSON_ID = t1.PERSON_ID
where t2.PHONE_TYPE in ('BUSINESS', 'PERSONAL', 'HOME') 
group by t1.PERSON_ID, t1.FIRST_NAME, t1.MIDDLE_NAME, t1.LAST_NAME;