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.

[Back to original message]