December 19, 2006 at 4:20 am
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 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.
Viking
December 19, 2006 at 4:59 am
If I'm guessing correctly, you're looking for rows where a row in #tab3 doesn't have a matching value of b1 in #tab2. (I think... it's very confusing that you don't preserve the same alphabetical and numerical order when naming your tables and columns!) Anyway, what you need to say is not WHERE c.b2 = '', but WHERE c.b2 IS NULL.
John
December 19, 2006 at 5:01 am
Hope your joins are right.
Try this clause:
WHERE c.b2 = @Criterion OR @Criterion = ''
If I understand your requirements correctly...
_____________
Code for TallyGenerator
December 19, 2006 at 8:00 am
Thanks to everybody for providing in thoughts. The query is working like a charm after using
WHERE c.b2 = @Criterion OR @Criterion = ''.
Viking
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply