CODEWITHSUNDEEP
sqloracle-database
John
John

Reputation: 69

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

Upvotes: 0

Views: 3538

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

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;

Upvotes: 1

Unoembre
Unoembre

Reputation: 555

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) )

Upvotes: 0

Related Questions