Solved

SQL Select Case

Posted on 2013-05-22
7
314 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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