I am having 2 tables. t1 and t2.
create table t1
(
r1 int,
n varchar(10)
);
create table t2
(
r2 int,
m int
)
the way I populate t1 is
r1--- n
.----------
1--- abc
and t2 is having the following data
r2--- m
.----------
1--- 46
2--- 50
3--- 100
I want to display all r2 values for corresponding r1.
Also mismatching things should also display but with 0.
I wrote a outer join query like this
select * from
t1 right join
t2
on t1.r1 = t2.r2
The ouput I am getting here is
r1------n------r2------m
.----------------------
1------ abc--- 1--- 45
null--- null--- 2--- 60
null--- null--- 3--- 100
But what I am expecting is a output like this
r1----n----r2----m
.----------------------
1--- abc--- 1--- 45
1--- abc--- 2--- 0
1--- abc--- 3--- 0
Could anyone propose a solution to achieve this.
If I adds one more row into t1, say 2---xyz
then the output should be
r1----n----r2----m
.----------------------
1--- abc--- 1--- 45
1--- abc--- 2--- 0
1--- abc--- 3--- 0
2---xyz---1---0
2---xyz---2---60
2---xyz---3---0
Your help would be much appreciated.
Regards
Anish Antony
I think you want to CROSS JOIN your tables:
SELECT t1.r1,
t1.n,
t2.r2,
CASE WHEN t1.r1 = t2.r2 THEN t2.m ELSE 0 END AS m
FROM t1
CROSS JOIN t2
ORDER BY t1.r1, t2.r2;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments