CODEWITHSUNDEEP
mysqldatabaseoracle-databasepostgresqldb2
Rama
Rama

Reputation: 19

Select single row with multiple rows in joined table

I have a contact table as below: 

contactid | contactname  
----------|-------------
C1        | Name1  
C2        | Name2

I have a communication table as below

contactid | communication_type | communication_string
----------|--------------------|---------------------
C1        | Phone              | 9090909090
C1        | Email              | [email protected]
C2        | Phone              | 9191919191
C2        | Email              | [email protected]

Now my requirement is to query these two tables such that the results are as below: 

contactid | contactname | phonenumber   | emailaddress
----------|-------------|---------------|----------------
C1        | Name1       | 9090909090    | [email protected]
C2        | Name2       | 9191919191    | [email protected]

If I do a regular join, as 

SELECT cont.contactid, cont.contactname, 
  comm.communication_type, comm.communication_string 
  FROM contact cont
  LEFT JOIN communication comm ON cont.contactid = comm.contactid

I will get something like 

contactid | contactname | communication_type| communication_string 
----------|-------------|-------------------|----------------
C1        | Name1       | Phone             | 9090909090
C1        | Name1       | Email             | [email protected]
C2        | Name2       | Phone             | 9191919191
C2        | Name2       | Email             | [email protected]

But this is not what I want. I want both the communication strings in the same row in the result and not on different rows.

Is this possible at all to get such a result.?

One more requirement is that the solution should be generic to work on all databases.

Upvotes: 1

Views: 73

Answers (2)

Andrei Bucurei
Andrei Bucurei

Reputation: 2438

You can join the same table multiple times with different conditions:

select   c.contactid
    ,c.contactname
    ,cp.comunication_string as 'phonenumber'
    ,ce.comunication_string as 'emailaddress'

from    contact c
        left join
        communication cp    on  c.contactid = cp.contactid
                            and cp.comunication_type = 'Phone'
        left join
        communication ce    on  c.contactid = ce.contactid
                            and ce.comunication_type = 'Email'

Standard SQL and easy to read.

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use conditional aggregation:

select cont.contactid,
    cont.contactname,
    max(case when comm.communication_type = 'Phone' then comm.communication_string end) PhoneNumber,
    max(case when comm.communication_type = 'Email' then comm.communication_string end) EmailAddress
from contact cont
left join communication comm on cont.contactid = comm.contactid
group by cont.contactid,
    cont.contactname;

This will return one Phonenumber and Emailaddress for given contactId.

This solution will work in most of RDBMS.

Upvotes: 2

Related Questions