Pad results with default values

Question

I'm trying to add records into a result dynamically if they don't exist and preserve the order. How would one go about adding additional values and making sure a record exists for [Foreign ID] | [Date] | [ID]?

For example if I do a select how can I guarantee that I will always have [ID] 1,2,3,4,5 in for all existing [Foreign ID] | [Date]? I found a related article however it's for postgress. It looks like I will need to understand and use coalesce.

**Schema**
[Foreign ID] | [Date] | [ID] | [Name]

Results...

11 | 2020-02-02 00:00:00.000 | 1 | 'Bob'
11 | 2020-02-02 00:00:00.000 | 2 | 'Alice'
12 | 2020-02-02 00:00:00.000 | 1 | 'Bob'
12 | 2020-02-02 00:00:00.000 | 2 | 'Alice' 
13 | 2020-02-03 00:00:00.000 | 1 | 'Bob'
14 | 2020-02-03 00:00:00.000 | 2 | 'Alice'
15 | 2020-02-03 00:00:00.000 | 1 | 'Bob'
16 | 2020-02-03 00:00:00.000 | 2 | 'Alice'

score 1 · Accepted Answer · answered Feb 11 '20 at 01:19

1

You can use cross join to get the rows and a left join to bring in the existing data:

select f.foreign_id, d.date, v.id, t.name
from (select distinct foreign_id from t) f cross join
     (select distinct date from t) d cross join
     (values (1), (2), (3), (4), (5)) v(id) left join
     t
     on t.foreign_id = f.foreign_id and
        t.date = d.date and
        t.id = v.id;

answered Feb 11 '20 at 01:19

Gordon Linoff

1,242,037
58
646
786

This is really close to meet my specifications I see some duplication from the joins. I'm running a stored procedure that joins all the tables into a result set. So in this case I'm doing all those cross joins and left joins on the same result set if that helps at all. I'll post some more on this tomorrow if that doesn't clarify the issue. – node943034835 Feb 11 '20 at 01:45
@node943034835 . . . This would only have "duplication" if your original table had duplicates. – Gordon Linoff Feb 11 '20 at 02:08
you're correct. The issue wasn't about duplication it was of extra unneeded data. I needed dates that also have the foreign ID else it would create more values than required. I changed the query to "(select distinct, date foreign_id from t) f cross join". If I wanted to replace the NULL values from these joins how would one do that? – node943034835 Feb 11 '20 at 20:18
I think I figured it our per [this] (https://stackoverflow.com/a/1664268/7999434) thanks again! – node943034835 Feb 11 '20 at 20:41

Pad results with default values

1 Answers1