Solved

SQL Select Case

Posted on 2013-05-22
7
312 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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 69

Expert Comment

by:ScottPletcher
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 40

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now