CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Satfactor
Satfactor

Reputation: 389

left outer join in t-sql

I have the following two tables. I am using SQL Server 2008 R2

Create table #tmp1 (
a char(1)
)

Create table #tmp2 (
id int,
a char(1),
val int
)

insert #tmp1 values ('A')
insert #tmp1 values ('B')
insert #tmp1 values ('C')

insert #tmp2 values (1,  'A', 10)
insert #tmp2 values (1,  'B', 20)
insert #tmp2 values (2,  'A', 30)
insert #tmp2 values (2,  'C', 40)

select * from #tmp1 t1 left outer join #tmp2 t2 on t1.a = t2.a
order by t2.id

This returns the result set

A   1   A   10
B   1   B   20
C   2   C   40
A   2   A   30

I would like to have the following result set

 A     1    A       10
 B     1    B       20
 C     1    null    null
 A     2    A       30
 B     2    null    null
 C     2    C       40

Right now i am acheiving this by creating a new table with a cross join like this and then doing a outer join

select * into #tmp3 from #tmp1 cross join (select distinct ID from #tmp2) t
select * from #tmp3 t1 left outer join #tmp2 t2 on t1.a = t2.a and t1.id = t2.id

Is there a better way to do this ?

Thanks

Upvotes: 8

Views: 1060

Answers (2)

Sebastian Meine
Sebastian Meine

Reputation: 11823

What you are looking for is a cartesian product of the values in #tbl1 and the values in the id column in #tbl2. Because the values in #tbl2.id are not unique it might be a better design to have an additional table with a row for each #tbl2.id value. Then you can use this solution:

Create table #tmp1 (
a char(1)
)

Create table #tmp2 (
id int,
a char(1),
val int
)

Create table #tmp3 (
id int
)

insert #tmp1 values ('A')
insert #tmp1 values ('B')
insert #tmp1 values ('C')

insert #tmp3 values (1)
insert #tmp3 values (2)

insert #tmp2 values (1,  'A', 10)
insert #tmp2 values (1,  'B', 20)
insert #tmp2 values (2,  'A', 30)
insert #tmp2 values (2,  'C', 40)

SELECT  t3.id,t1.a,t2.val
FROM    #tmp1 AS t1
CROSS JOIN #tmp3 AS t3
LEFT OUTER JOIN #tmp2 AS t2
ON t1.a = t2.a AND t3.id = t2.id
ORDER BY t3.id, t1.a;

If that is not an option use this instead:

SELECT  t3.id,t1.a,t2.val
FROM    #tmp1 AS t1
CROSS JOIN (SELECT DISTINCT id FROM #tmp2) AS t3
LEFT OUTER JOIN #tmp2 AS t2
ON t1.a = t2.a AND t3.id = t2.id
ORDER BY t3.id, t1.a;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

To get what you want, you need a "driving" table. That is, you want a complete list of all combinations, and then to join to the other tables to get the matches. Here is one way:

select t1.a, t2.*
from (select t1.a as a, t2.id as id
      from (select distinct a from #tmp1 t1) t1
           cross join
           (select distinct id from #tmp2 t2) t2
     ) driving left outer join
     #tmp1 t1
     on t1.a = driving.a left outer join
     #tmp2 t2
     on t2.id = driving.id and
        t2.a = driving.a
order by t2.id

Upvotes: 3

Related Questions