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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy