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
A left outer join is a method of combining tables. The result includes unmatched rows from only the table that is specified before the LEFT OUTER JOIN clause. If you are joining two tables and want the result set to include unmatched rows from only one table, use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause.
There really is no difference between a LEFT JOIN and a LEFT OUTER JOIN. Both versions of the syntax will produce the exact same result in PL/SQL. Some people do recommend including outer in a LEFT JOIN clause so it's clear that you're creating an outer join, but that's entirely optional.
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
The syntax for the LEFT OUTER JOIN in MySQL is: SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With