CODEWITHSUNDEEP
sqldatabasepostgresqlfull-outer-join
Sumit Singh
Sumit Singh

Reputation: 129

full outer join on two keys

I am trying to do merge two tables on phone numbers so that if I find phone in either of the tables then join the rest of the fields as shown below. Now there are scenarios where phone doesn't exist in both the tables. Then the table should join on email_id, so basically first check if phone matches if not then check for email id match. If none then drop the record.

select COALESCE(icici.phone, hsbc.phone) as phone,
    COALESCE(icici.email_id, hsbc.email_id) as email_id, city
from credit_card.icici
full outer join credit_card.hsbc on icici.phone = hsbc.phone
                or icici.email_id = hsbc.email_id 
    limit 10

But I am getting this error

ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions

SQL state: 0A000

Is there a way to solve it, or is there a better way to do this?

Upvotes: 2

Views: 1504

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

Use union all and aggregation:

select phone, max(email_id)
       maxCOALESCE(icici.phone, hsbc.phone) as phone,
    COALESCE(icici.email_id, hsbc.email_id) as email_id, city
from ((select phone, email_id
       from credit_card.icici
      ) union all
      (select phone, email_id
       from credit_card.hsbc
      ) 
     ) cc
group by phone,
         (case when phone is null then email_id end);

Upvotes: 0

clemens
clemens

Reputation: 17722

You can union the result of a left and a right join:

SELECT COALESCE(icici.phone, hsbc.phone) as phone,
    COALESCE(icici.email_id, hsbc.email_id) as email_id, city
FROM credit_card.icici
    LEFT OUTER JOIN credit_card.hsbc on icici.phone = hsbc.phone
                    OR icici.email_id = hsbc.email_id 
UNION (
    SELECT COALESCE(icici.phone, hsbc.phone) as phone,
        COALESCE(icici.email_id, hsbc.email_id) as email_id, city
    FROM credit_card.icici
        RIGHT OUTER JOIN credit_card.hsbc on icici.phone = hsbc.phone
                         OR icici.email_id = hsbc.email_id
    WHERE icici.id IS NULL
)

However, the right join may only contain the rows that were not found for any values from the left table. These rows are filtered out using WHERE, for example, by checking the primary key for NULL.

Upvotes: 1

Related Questions