?
Solved

SQL Select Case

Posted on 2013-05-22
7
Medium Priority
?
321 Views
Last Modified: 2013-06-04
I have a Select statement that returns user messages, please see code below.
As you see I (try to) use a Select Case method to set correct values to "DeletedId" and "DeletedOut". What I want to do is this:

1. If [To]=@To then DeletedId must be 0, else DeletedId can be 0 or 1 (doesn't matter).
2. If [From]=@From then DeletedOut must be 0, else DeletedOut can be 0 or 1 (doesn't matter).

		
SELECT Id, [From], [To] FROM tblUserMessage
WHERE ([To] = @To OR [From] = @From) AND (SiteId = @SiteId) AND (Deleted = '0') AND DeletedIn = (Case when [To] = @To Then '0' else '[0 or 1]' END) AND DeletedOut = (Case when [From] = @From Then '0' else '[0 or 1]' END)

Open in new window

Hope someone please can help :)
0
Comment
Question by:webressurs
7 Comments
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187185
I would do you case statement on a select within a select e.g

Select
*
from
(
Select
Case
where field = field then 'something'
else 'other'
End as [Field]
from [YourTable]

) as Temp

where <inset your variables here>
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187213
i've created this example to give you a better idea of what i'm talking about

http://sqlfiddle.com/#!3/fc87a/3
0
 
LVL 1

Author Comment

by:webressurs
ID: 39187329
Thanks for your reply RossTurner!
I did not fully understand your example, hope is it possible to get some help with my code below.

SELECT Id, [From], [To] FROM tblUserMessage
WHERE ([To] = 'jason' OR [From] = 'jason') AND (SiteId = '2') AND (Deleted = '0') AND DeletedIn = (Case when [To] = 'jason' Then '0' else '[DONT CARE]' END) AND DeletedOut = (Case when [From] = 'jason' Then '0' else '[DONT CARE]' END)            

Thanks :)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Accepted Solution

by:
webressurs earned 0 total points
ID: 39187348
I guess this is the solution I'm looking for?

SELECT Id, [From], [To] FROM tblUserMessage
WHERE ([To] = 'jason' OR [From] = 'jason') AND (SiteId = '2') AND (Deleted = '0') AND DeletedIn = (Case when [To] = 'jason' Then '0' else DeletedIn END) AND DeletedOut = (Case when [From] = 'jason' Then '0' else DeletedOut END)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39188390
SELECT Id, [From], [To]
FROM dbo.tblUserMessage
WHERE
    ([To] = 'jason' OR [From] = 'jason') AND
    (SiteId = '2') AND
    (Deleted = '0') AND
    ([To] <> 'jason' OR DeletedIn = '0') AND
    ([From] <> 'jason' OR DeletedOut = '0')
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39189429
If you have data like this. Can you let us know which records you want? DO you want all of them?
To	From	DeletedIn	DeletedOut
jason	jason	0	0
jason	jason	1	0
jason	jason	0	1
jack	jason	0	0
jack	jason	1	0
jason	jack	0	0
jason	jack	0	1

Open in new window

0
 
LVL 1

Author Closing Comment

by:webressurs
ID: 39218373
This is the correct solution.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question