CODEWITHSUNDEEP
sqljointeradata
M PAUL
M PAUL

Reputation: 1248

Single column from Multiple columns from join in SQL

I have a table output like this, which is a result of multiple table joins.

+---------+------+--------+------+
|   Id    | V1   | V2     | V3   | 
+---------+------+--------+------+
|    1    | x1   | null   |  null|
|    2    | x2   | null   |  null|
|    3    | null | x3     |  null|
|    4    | null | x4     |  null|
|    5    | null | null   |  x9  |
+---------+------+--------+------+

I am trying to get a table like this.

+---------+------+
|   Id    | V    |  
+---------+------+
|    1    | x1   | 
|    2    | x2   | 
|    3    | x3   |
|    4    | x4   |
|    5    | x5   |
+---------+------+

This is what i am doing currently doing. Not sure how to make the three columns combine to a single column.

select a.identifier, a.v1, b.v2, c.v3,
from table a
full join table b on a.identifier = b.identifier
full join table c on a.identifier = c.identifier
where a.v REGEXP 'some condition'

Upvotes: 0

Views: 133

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If you have only one value per row -- or if you just want the first one-- then use coalesce():

select id, coalesce(v1, v2, v3) as v
from t;

Upvotes: 1

Manoj Mohandas
Manoj Mohandas

Reputation: 21

Do have a look at - COALESCE() Function, it returns the first non-null value in a list.

Upvotes: 1

Related Questions