CODEWITHSUNDEEP
sqloracle-database
user3802500
user3802500

Reputation: 23

Issue using Inner/Outer Joins in a query

Here's is my main table. where i have three column with there dates. some of them are NULL values. i want to have outputt some thing like given below. i tried alot but couldnt get the correct answer. if someone can help. thanks.

a.id  a.date       b.id   b.date      c.id   c.date
3     7/1/2014     11     NULL        21     NULL
5     8/1/2014     12     NULL        22     NULL
5     8/1/2014     13     NULL        22     NULL
5     8/1/2014     12     NULL        23     NULL
5     8/1/2014     13     NULL        23     NULL
6     NULL         14     06/01/2014  24     NULL
7     NULL         15     NULL        25     09/01/2014

Desired Output

a.id  b.id   c.id   date
3     11     21     07/01/2014
5     12     22     08/01/2014
5     13     22     08/01/2014
5     12     23     08/01/2014
5     13     23     08/01/2014
6     14     24     06/01/2014
7     15     25     09/01/2014

Upvotes: 1

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Just use coalesce():

select a.id, b.id, c.id, coalesce(a.date, b.date, c.date) as date

Upvotes: 1

Robert
Robert

Reputation: 25763

You are looking for coalesce funtcion

select a.id,bid,c.id, 
       coalesce(a.date,b.date,c.date) as date
from ...

Upvotes: 1

Related Questions