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: