Conditional query results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conditional query results

Author

Topic

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2006-12-19 : 06:13:11

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.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-19 : 06:33:53

are you maybe confusing nulls with empty strings?

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

Duane.

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2006-12-19 : 06:41:57

I tried the option you had given but it returns me only the third row.

3 NULL

Infact, 100 or 200 or '' can be input parameters to the sp where this t-sql is going to sit.

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-19 : 06:48:11

Which parameters did you run it with?

Did you try using IN in stead of OR

coalesce(c.b2, '') IN ('100' , '200' ,'')

You should also perhaps look at a way of not building the sql in your app, but rather calling a storedproc passing it these parameters.

Duane.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-19 : 07:02:08

How about this:

select A.a1, D.d2
from 	#tab1 A left Join #tab3 C on A.a1 = C.c1
	left Join #tab2 B on C.c2 = B.b1
	Left Join #tab4 D on A.a1 = D.d1
where
	1 = case @param when '' then 1 else
		case when B.b2 = @param then 1 else 0 end
                 end

@param - input parameter

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2006-12-19 : 09:58:53

Harsh, thanks the query works like a charm and duane thanks for setting the conversation to a start off mode.

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources