Prevent duplicate values in left join

I faced a situation where I got duplicate values from left join. I think this might be a desired behavior but unlike from what I want.
Description-
I have thee tables- person, department and contact.

person

id bigint,
person_name character varying(255)

department

person_id bigint,
department_name character varying(255)

contact

person_id bigint,
phone_number character varying(255)

Sql Query-

select p.id, p.person_name, d.department_name, c.phone_number from person p
left join department d on p.id = d.person_id
left join contact c on p.id = c.person_id;

Result-

id|person_name|department_name|phone_number
-------------------------------------------
1 |"jhon"     |"Finance"      |"023451"
1 |"jhon"     |"Finance"      |"99478"
1 |"jhon"     |"Finance"      |"67890"
1 |"jhon"     |"Marketing"    |"023451"
1 |"jhon"     |"Marketing"    |"99478"
1 |"jhon"     |"Marketing"    |"67890"
2 |"barbara"  |"Finance"      |""
3 |"Michell"  |""             |"005634"

I know its what joins do, keeping multiplied with selected rows. But It gives a sense like phone numbers 023451,99478,67890 are for both departments while they are only related to person john with unnecessary repeated values which will escalate the problem with larger data set. So, here is what I want-

id|person_name|department_name|phone_number
-------------------------------------------
1 |"jhon"     |"Finance"      |"023451"
1 |"jhon"     |"Marketing"    |"99478"
1 |"jhon"     |""             |"67890"
2 |"barbara"  |"Finance"      |""
3 |"Michell"  |""             |"005634"

This is a sample of my situation and I am using a large set of tables and queries. So, kind of need a generic solution. Any help will be appreciated.


Source: sql

Leave a Reply