Solved

SQL Select Case

Posted on 2013-05-22
7
313 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

770 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