|
|
Posted by msrviking on 12/19/06 11:33
Hello everybody,
After several attempts of writing the query, I had to post my
requirement in the forum.
Here is what I have, what I need and what I did.
Table A
Col1 Col2
1 Nm1
2 Nm2
3 Nm3
Table B
Col1 Col2
10 100
20 200
Table C
Col1 (A.Col1) Col2 (B.Col1)
1 10
2 10
Table D
Col1 (A.Col1) Col2
1 Value1
2 Value2
I need results based on below criteria,
1.
Criteria - B.Col2 = 100
Resultset
A.Col1 D.Col1
1 Value1
2 Value2
2.
Criteria - B.Col2 =""
A.Col1 D.Col1
1 Value1
2 Value2
3 NULL
3.
Criteria - B.Col2 =200
Empty resultset
Here is the query I tried, but looks its not working. Probably there is
a better way to do this.
DDL and DML statements:
create table #tab1 (a1 int, a2 nvarchar(20))
create table #tab2 (b1 int, b2 int)
create table #tab3 (c1 int, c2 int)
create table #tab4 (d1 int, d2 nvarchar(20))
insert into #tab1 values (1, 'nm1')
insert into #tab1 values (2, 'nm2')
insert into #tab1 values (3, 'nm3')
insert into #tab2 values (10, 100)
insert into #tab2 values (20, 200)
insert into #tab3 values (1, 10)
insert into #tab3 values (2, 10)
insert into #tab4 values (1, 'value1')
insert into #tab4 values (2, 'value2')
select
a.a1
, d.d2
from #tab1 a
left join #tab3 b
on a.a1 = b.c1
left join #tab2 c
on b.c2 = c.b1
left join #tab4 d
on a.a1 = d.d1
where
c.b2 = [100 or 200 or ''] or exists (select 1 from #tab4 d
where a.a1 = d.d1
and c.b2 = [100 or 200 or ''] )
The above query works well to give results for Criteria 1 and Criteria
3, but doesn't return for '' (criteria 2). I couldn't manage cracking
the solution. I shall try once again, but meanwhile if anyone could
help me in this, that would be great.
Thanks.
Navigation:
[Reply to this message]
|