Add missing rows to a result set

问题:

I have a fact and dim table

create table #fact (SKey int, HT varchar(5), TitleId int)

insert into #fact values
(201707, 'HFI', 1),
(201707, 'HFI', 3),
(201707, 'HFI', 5),
(201707, 'HFI', 6),
(201707, 'REO', 1),
(201707, 'REO', 2),
(201707, 'REO', 4),
(201707, 'REO', 5)

create table #dim (TitleId int, Title varchar(10))
insert into #dim values
(1, 'UK'),
(2, 'AF'),
(3, 'LQ'),
(4, 'AL'),
(5, 'GT'),
(6, 'ML')

using below query

select #fact.SKey, #fact.HT, #fact.TitleId, #dim.Title
from #fact
    inner join #dim on #dim.TitleId = #fact.TitleId
order by #fact.SKey, #fact.HT, #fact.TitleId, #dim.Title

which returns me following data

   SKey    HT    TitleId   Title  
 -------- ----- --------- ------- 
  201707   HFI         1   UK     
  201707   HFI         3   LQ     
  201707   HFI         5   GT     
  201707   HFI         6   ML     
  201707   REO         1   UK     
  201707   REO         2   AF     
  201707   REO         4   AL     
  201707   REO         5   GT     

You see there are missing Titles in the result. for example, I don't have 'AF' and 'AL' for the first set ('HFI' set) and don't have 'LQ' and 'ML' for 'REO' part.

In summary I'm going to generate below result

   SKey    HT    TitleId   Title  
 -------- ----- --------- ------- 
  201707   HFI         1   UK     
  201707   HFI         2   AF     -- missing from first result
  201707   HFI         3   LQ     
  201707   HFI         4   AL     -- missing from first result
  201707   HFI         5   GT     
  201707   HFI         6   ML     
  201707   REO         1   UK     
  201707   REO         2   AF     
  201707   REO         3   LQ     -- missing from first result
  201707   REO         4   AL     
  201707   REO         5   GT     
  201707   REO         6   ML     -- missing from first result

currently I'm store the first result into a temp table and then use a loop/cursor to add missing rows into int.

Is there any way we use just one query to get the final result?

回答1:

Possibly a cross join like:

;with f as (
  select SKey, HT from fact
  group by SKey, HT
)
select f.SKey, f.HT, dim.TitleId, dim.Title 
from f, dim;

Here is the sql fiddle.

回答2:

I think you want to change that to an outer join.

select #fact.SKey, #fact.HT, #fact.TitleId, #dim.Title
from #fact
    left join #dim on #dim.TitleId = #fact.TitleId
order by #fact.SKey, #fact.HT, #fact.TitleId, #dim.Title