SQL Select Case

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 :)
LVL 1
webressursAsked:
Who is Participating?
 
webressursConnect With a Mentor Author Commented:
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
 
Ross TurnerManagement Information Support AnalystCommented:
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
 
Ross TurnerManagement Information Support AnalystCommented:
i've created this example to give you a better idea of what i'm talking about

http://sqlfiddle.com/#!3/fc87a/3
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
webressursAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
SharathData EngineerCommented:
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
 
webressursAuthor Commented:
This is the correct solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.