CODEWITHSUNDEEP
sql-servert-sqlleft-join
paparazzo
paparazzo

Reputation: 45096

Left join on a table variable fails

See below I do not get a row for each stats
This left join acts like an inner join
I get 6 rows and expect 9
I want 3 rows for each name (null data if there is no match)
How to fix this? 

declare @table table (name varchar(10), status int, data int);
insert into @table values 
  ('a', 1, 2)
, ('a', 2, 5)
, ('a', 3, 7)
, ('b', 1, 5) 
, ('b', 2, 6)
, ('c', 1, 3) 

select stats.status as statusStats
     , t.status as statusData, t.name, t.data
from (values (1),(2),(3)) as stats(status)
left join @table t 
  on t.status = stats.status

Desired output 

  ('a', 1, 2)
, ('a', 2, 5)
, ('a', 3, 7)
, ('b', 1, 5) 
, ('b', 2, 6)
, ('b', 3, null)
, ('c', 1, 3) 
, ('c', 2, null)
, ('c', 3, null)

Upvotes: 1

Views: 1368

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

You are probably expecting a cartesian product between name and status values. Well, bad luck - SQL can't "guess" this, you have to introduce the multiplication manually:

select stats.status as statusStats, t.status as statusData, t.name, t.data
from (values (1),(2),(3)) stats(status)
  cross join (values ('a'),('b'),('c')) names(name)
left join @table t 
  on t.status = stats.status
  and t.name = names.name;

Upvotes: 3

Related Questions