473,325 Members | 2,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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
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.

Dec 19 '06 #1
3 3725
ms*******@gmail.com wrote:
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/Re...tional-columns

Dec 19 '06 #2
(ms*******@gmail.com) writes:
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 19 '06 #3
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.

Dec 21 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: neblackcat | last post by:
Would anyone like to comment on the following idea? I was just going to offer it as a new PEP until it was suggested that I post it here for comment & consideration against PEP 308. I'm far...
8
by: Guy Hocking | last post by:
Hi there, I am having a few problems compiling a list box that is conditional on what is selected in another list box. What i need is a List box (lstArea) that displays one thing when the List...
10
by: Ilik | last post by:
Hi all I'm trying to create a summery table of rain data that holds a record for every 0.1mm of rain in the following format: Station Name, Time, Value A, 2002-12-03 14:44:41.000, 0.1 A,...
4
by: Steve | last post by:
How do I write the conditional If statement for a query criteria where if Forms!MyForm!MyCbx = "AllCategories" then Like "*" else Forms!MyForm!MyCbx ? I can't get the Like "*" to work. Thanks!...
5
by: John Baker | last post by:
Hi: I have a field that i wish to use the conditional format capability on, and for some reason it wont work. The field is a a text box: =- I would like to make it red when negative, and...
5
by: googleboy | last post by:
I am trying to create a query that will perform a set of queries in a sequence. If the results of the first query are null, then I want the query to continue to the next query. In all, I believe the...
8
by: Typehigh | last post by:
I have many text fields with conditional formatting applied, specifically when the condition is "Field Has Focus". Without any events associated with the fields the conditional formatting works...
4
by: midlothian | last post by:
Hello, I have conditional formatting set up on a subform based on a calculated value in the underlying query. For instance, if Sales are >$1000, the query displays "Yes," otherwise it displays...
6
by: Jared | last post by:
Consider the following two functionally identical example queries: Query 1: DECLARE @Name VARCHAR(32) SET @Name = 'Bob' SELECT * FROM Employees WHERE = CASE WHEN @Name IS NULL THEN ELSE...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.