CODEWITHSUNDEEP
sqlpostgresqljoinnull
RevXaisks
RevXaisks

Reputation: 183

How to combine (merge) similar columns to remove NULLs via JOIN

Problem Brief:

I have a superclass table called "Customers" and two child tables that inherit from Customers called "Person" and "Company". So that, Customer entity has a one-to-one relationship with "Person" or "Company".

(a Customer can only be of "Person" or of "Company", but not of both)

This is represented below:

Customer                    Person                      Company                
+-------+------+------+     +-------+------+------+     +-------+------+------+
|    cID|  col2|  col3|     |    cID| fname| sname|     |    cID|  name|  col3|
+-------+------+------+     +-------+------+------+     +-------+------+------+
|1      |?     |?     |     |1      |JJ    |AZ    |     |4      |ABCD  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|2      |?     |?     |     |2      |CC    |LL    |     |5      |BCDE  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|3      |?     |?     |     |3      |OO    |BB    |     |6      |CDEF  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|4      |?     |?     |     |7      |JK    |NN    |     |8      |DEFG  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|5      |?     |?     |     |9      |RR    |LW    |     |...    |EFGH  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|6      |?     |?     |     |10     |GN    |QN    |     |...    |FGHI  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|7      |?     |?     |     |...    |XC    |YU    |     |...    |GHIJ  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|8      |?     |?     |
+-------+------+------+
|9      |?     |?     |
+-------+------+------+
|10     |?     |?     |
+-------+------+------+
|...    |?     |?     |
+-------+------+------+

Intention & Attempt:

What I would like to do is query the database so that I can select the ID from Customer table, join to both Person and Company to retrieve the name attribute.

Below is what I have tried:

SELECT      tc."cust_id",
            CONCAT(tp."forename", ' ', tp."surname") AS "name",
            tcp."name"
FROM        "tbl_customer" AS tc
            LEFT JOIN "tbl_person" AS tp
                ON tc."cust_id" = tp."cust_id"
            LEFT JOIN "tbl_company" AS tcp
                ON tc."cust_id" = tcp."cust_id"

Executing the above SQL gives the following result, and to the right of is what I am looking to achieve:

Result                      Result          
+-------+------+------+     +-------+------+
|    cID|  name|  name|     |    cID|  name|
+-------+------+------+     +-------+------+
|1      |JJAZ  |null  |     |1      |JJAZ  |
+-------+------+------+     +-------+------+
|2      |CCLL  |null  |     |2      |CCLL  |
+-------+------+------+     +-------+------+
|3      |OOBB  |null  |     |3      |OOBB  |
+-------+------+------+     +-------+------+
|4      |null  |ABCD  |     |4      |ABCD  |
+-------+------+------+     +-------+------+
|5      |null  |BCDE  |     |5      |BCDE  |
+-------+------+------+     +-------+------+
|6      |null  |CDEF  |     |6      |CDEF  |
+-------+------+------+     +-------+------+
|7      |JKNN  |null  |     |7      |JKNN  |
+-------+------+------+     +-------+------+
|8      |null  |DEFG  |     |8      |DEFG  |
+-------+------+------+     +-------+------+
|9      |RRLW  |null  |     |9      |RRLW  |
+-------+------+------+     +-------+------+
|10     |GNQN  |null  |     |10     |GNQN  |
+-------+------+------+     +-------+------+
|...    |?     |?     |     |...    |?     |
+-------+------+------+     +-------+------+

Description:

As indicated, I am trying to combine the name of both Person and Company under the same column. Standard JOIN on both tables will not work, as it will return NULL results. Due to the nature of LEFT JOIN, NULL values are to be expected. This can be resolved very simply with SQL UNION and I know the solution to, however I am looking for an alternative to the UNION operator via JOIN.

Is there anyway I can perform the JOIN, group/ merge the name columns? Or something similar? But not have to use SQL UNION?

Update:

Both answers from Juan Carlos Oropeza and Becuzz are equally acceptable.

Upvotes: 4

Views: 1304

Answers (2)

Becuzz
Becuzz

Reputation: 6866

COALESCE sounds like it will do exactly what you want. COALESCE is a function which returns the first non-NULL value of its arguements.

SELECT      tc."cid",
            COALESCE(tp.firstName||' '||tp.lastName, tcp.name) as "name"
FROM        "customers" AS tc
            LEFT JOIN "person" AS tp
                ON tc."cid" = tp."cid"
            LEFT JOIN "company" AS tcp
                ON tc."cid" = tcp."cid"

SQL Fiddle

Upvotes: 6

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Just include a CASE to select what data use

SELECT      tc."cust_id",
            CASE WHEN tp."forename" IS NULL 
                   THEN tcp."name"
                 ELSE  CONCAT(tp."forename", ' ', tp."surname") 
            AS "name"
FROM        "tbl_customer" AS tc
            LEFT JOIN "tbl_person" AS tp
                ON tc."cust_id" = tp."cust_id"
            LEFT JOIN "tbl_company" AS tcp
                ON tc."cust_id" = tcp."cust_id"

Upvotes: 3

Related Questions