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.msrviking@.gmail.com wrote:
Quote: Originally Posted by 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. |
see http://www.sqlhacks.com/index.php/R...itional-columns|||(msrviking@.gmail.com) writes:
Quote: Originally Posted by 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. |
Thanks for posting the CREATE TABLE and INSERT statements. That makes
it easy to test. Here is a solution that gives the desired result. Since
B.Col2 is numeric, it cannot be a string value, so I am assuming NULL
for this case.
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')
go
create procedure #testie @.val int AS
select a.a1, d.d2
from #tab1 a
left join #tab4 d ON a.a1 = d.d1
WHERE @.val IS NULL OR
EXISTS (SELECT *
FROM #tab3 c
JOIN #tab2 b ON c.c2 = b.b1
WHERE c.c1 = a.a1
AND b.b2 = @.val)
go
EXEC #testie 100
EXEC #testie NULL
EXEC #testie 200
go
drop table #tab1, #tab2, #tab3, #tab4
drop proc #testie
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Can you fix up tyhis DDL? You seem to tell us that in Table C, we have
a VIEW of A.col1 and B.col1, but not rule for building this VIEW. You
also have no DDL delcaring keys and all columns are NULL-able so these
are not really tables at all! Is Table D a PK-FK relationship? In
which direction? Where did all those temp tables come from? Why do so
many of the data elements have the same names?
Just trying to make the abstrations human readable and put in questions
on the lack of specs:
CREATE TABLE Alpha
(alpha_id INTEGER NOT NULL PRIMARY KEY. - wild guess!!
alpha_name CHAR(5) NOT NULL);
CREATE TABLE Beta
(beta_id INTEGER NOT NULL PRIMARY KEY. -- wild guess!!
col2 INTEGER NOT NULL);
CREATE VIEW Gamma (alpha_id, beta_id)
AS
SELECT alpha_id, beta_id
FROM Alpha, Beta
WHERE << unknown search condiition>>; -- not possible to guess
CREATE TABLE Delta
(alpha_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Alpha(alpha_id), -- or is this refernced by Alpha?
delta_name CHAR(6) NOT NULL);
Your first criteria wants four columns back, your second criteria wants
five columns back.
But tables do not have a variable number of columns, so this makes no
sense. Oh, even empty result sets have columns, which you did not show
in your vague personal narrative.
The correct syntax is "x IN (<list of expressions>)" and not "x = [exp1
OR exp2 OR ..]
Whenteh specs are this bad and vague, the usual answer is that the DDL
is a nightmare.
