user3283107 user3283107 - 2 months ago 9
SQL Question

Creating a View table that override NULL values

I am having trouble creating a view using SQL server 2008. I have several tables that looks like that:

Parent Table:

+----+------+
| id | date |
+----+------+
| 1 | 2010 |
+----+------+


Child Table:

+----+------+-----------+
| id | date | ParentID |
+----+------+-----------+
| 1 | NULL | 1 |
| 2 | 2011 | 1 |
+----+------+-----------+


What I'm trying to achieve is a view that would look like that (based on the two previous examples) :

Child Table CustomView:

+----+------+----------+
| id | date | ParentID |
+----+------+----------+
| 1 | 2010 | 1 |
| 2 | 2011 | 1 |
+----+------+----------+


Basically, the Child table have some fields that should be 'inherited' from its Parent when its value is NULL (first row of Child table: '2010' is retrieved).
Otherwise, it should just display its value (second row of Child table: '2011').

Answer

use coalesce. you can have as many parameters as you like. it returns the first one that is not null. when all of them are null, it returns null

select c.[id],
       coalesce(c.[date], p.[date]) [date],
       c.parentid
  from child c
  join parent p
    on p.[id] = c.parentid