complex join question
-
Is there any better way to get my 3 row result set?
create
table t1(
col1
varchar(5) null,
col2
int null
)
create
table t2(
col1
varchar(5) null,
col2
int null
)
insert
into t1(col1, col2) values('A', 10)
insert
into t1(col1, col2) values('A', 11)
insert
into t1(col1, col2) values('B', 11)
insert
into t1(col1, col2) values('C', 12)
insert
into t1(col1, col2) values('C', 13)
insert
into t1(col1, col2) values('C', 21)
insert
into t1(col1, col2) values('D', 10)
insert
into t1(col1, col2) values('D', 13)
insert
into t2(col1, col2) values('A', 10)
insert
into t2(col1, col2) values('A', 20)
insert
into t2(col1, col2) values('A', 30)
insert
into t2(col1, col2) values('B', 11)
insert
into t2(col1, col2) values('C', 12)
insert
into t2(col1, col2) values('C', 21)
insert
into t2(col1, col2) values('D', 13)
select
a1.t1_col1, a1.t1_col2 from
(
select
t1.col1 as t1_col1, t1.col2 as t1_col2, t2.col1 as t2_col1, t2.col2 as t2_col2 from t1
left
join t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2
)
a1 where a1.t2_col1 is null
-
select t1.col1 as t1_col1, t1.col2 as t1_col2,
from t1
left join t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2
where t2.col1 is null
OR
select t1.col1 as t1_col1, t1.col2 as t1_col2,
from t1
WHERE NOT EXISTS (
select 1 from t2
where t1.col1 = t2.col1 and t1.col2 = t2.col2
)
_____________
Code for TallyGenerator -
Doesn't seem that complex at all. Unless we're missing something.
--------------------
Colt 45 - the original point and click interface
-
thanks that is better, don't know why this puzzled me, its been a long two weeks...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply