Conditional query results

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
Col1Col2
1Nm1
2Nm2
3Nm3

Table B
Col1Col2
10100
20200

Table C
Col1 (A.Col1)Col2 (B.Col1)
110
210

Table D
Col1 (A.Col1)Col2
1Value1
2Value2

I need results based on below criteria,

1.
Criteria - B.Col2 = 100
Resultset
A.Col1D.Col1
1Value1
2Value2

2.
Criteria - B.Col2 =""
A.Col1D.Col1
1Value1
2Value2
3NULL

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 2,

but doesn't return for ''. 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.

Change the select query as follow as...

1. You should convert your Integer Column into Varchar to compare with '',

2. You should apply the Isnull function to match the ''

I hope it will work for you...

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
Isnull(Convert(varchar,c.b2),'') in (100 ,200,'')
or exists
(select 1 from #tab4 d where a.a1 = d.d1 and Isnull( Convert(varchar,c.b2),'') in (100, 200 , ''))