sqlpostgresqlsybase

SQL Query To Fill in "missing rows" in a view

I have a table in a SQL database where rows are not stored if all values are default.

Currently this is in Sybase but it also needs to work in Postgres in the near future.

i.e:

Parties

Key Other data
1 sdsd
2 asdsa
3 fgdfgd

Config

Key T d1 d2
1 a 1 2
1 b 3 4
1 c 5 6
2 a 7 8
2 b 1 2
2 c 1 2
3 a 1 2
3 b 1 2
3 c 1 2

For performance reasons, if the default values are d1=1, d2=2 then those are not saved in the database. So the actual saved data looks like this:

Config

Key T d1 d2
1 b 3 4
1 c 5 6
2 a 7 8

I'm trying to write a view that "fills in" this missing data dynamically and can't work out how to do it.

Basically for every row in the parties table I want to see three rows row in the view - one for each of a,b,c - and it should fill in 1,2 for d1 and d2.

My first thought was to create a temporary table containing:

#T
a
b
c

It feels like I should then be able to do this with outer joins onto that table, but I can't get the database to cooperate. In particular the additional rows never appear in the result set no matter how I try to do the join.

Alternatively, doing it as a Union might work for a single Party but I don't see how to do it to fill in the missing rows for every Party.

Is there any way to do this, please?

Solution

  • If running Sybase ASE, or any other database, where CTEs are not supported ...

    Start by generating a dynamic table of all possible T characters:

    select 'a' as T union select 'b' union select 'c'
go

 T 
 - 
 a 
 b 
 c

    Next we generate a cartesian product (aka cross join) with the parties table:

    select  p2.[Key],
        dt2.T as T
from    parties p2
join    (select 'a' as T union select 'b' union select 'c') dt2
on      1=1
order by 1,2
go

 Key         T 
 ----------- - 
           1 a 
           1 b 
           1 c 
           2 a 
           2 b 
           2 c 
           3 a 
           3 b 
           3 c

    NOTES:

    For the main attraction we perform a left join from this cartesian product to the config table:

    select  dt1.[Key],
        dt1.T,
        isnull(c.d1,1) as d1,
        isnull(c.d2,2) as d2

from    (select p.[Key],
                dt2.T
         from   parties p
         join   (select 'a' as T union select 'b' union select 'c') dt2
         on     1=1
        ) dt1

left
join    config c
on      dt1.[Key] = c.[Key]
and     dt1.T     = c.T

order by 1,2
go

 Key         T d1          d2          
 ----------- - ----------- ----------- 
           1 a           1           2 
           1 b           3           4 
           1 c           5           6 
           2 a           7           8 
           2 b           1           2 
           2 c           1           2 
           3 a           1           2 
           3 b           1           2 
           3 c           1           2

    NOTES: