CODEWITHSUNDEEP
sqlsql-serversql-server-2008
cc0
cc0

Reputation: 1950

SQL select statement filtering

Ok, so I'm trying to select an amount of rows from a column that holds the value 3, but only if there are no rows containing 10 or 4, if there are rows containing 10 or 4 I only want to show those.

What would be a good syntax to do that? So far I've been attempting a CASE WHEN statement, but I can't seem to figure it out.

Any help would be greatly appreciated.

(My database is in an MS SQL 2008 server)

Upvotes: 0

Views: 12923

Answers (4)

Tomalak
Tomalak

Reputation: 338148

FYI: Orginal question title was "SQL CASE WHEN NULL - question"

CASE WHEN YourColumn IS NULL THEN x ELSE y END

Since there is nothing that compares to NULL and returns true (not even NULL itself), you cant't do

CASE YourColumn WHEN NULL THEN x ELSE y END

only

CASE ISNULL(YourColumn, '') WHEN '' THEN x ELSE y END

but then you lose the ability to differentiate between NULL and the (in this example) empty string.

Upvotes: 2

Adam Robinson
Adam Robinson

Reputation: 185613

Use a union all:

select
    // columns
from YourTable
where YourColumn = 3 and not exists (
    select 1 from YourTable where YourColumn = 10 or YourColumn = 4)

union all

select
    // columns
from YourTable
where YourColumn = 10 or YourColumn = 4

Upvotes: 4

Bill Karwin
Bill Karwin

Reputation: 562260

The simplest solution would be to do this in two queries:

SELECT ... FROM YourTable WHERE SomeColumn IN (10,4)

If and only if the above query yields no results, then run the second query:

SELECT ... FROM YourTable WHERE SomeColumn = 3

Running two queries may seem "inelegant" but it has advantages:

  • It's easy to code
  • It's easy to debug
  • It often has better performance than a very complex solution
  • It's easy to understand for a programmer who has to maintain the code after you.

Running two queries may seem like it has extra overhead, but also consider that you won't run the second query every time -- only if the first query has an empty result. If you use an expensive single-query solution, remember that it will incur that expense every time.

Upvotes: 1

cortijon
cortijon

Reputation: 983

Depending on the size of your table and its indexes, it may be more efficient to calculate which values you want before the query

declare @UseThree as bit = 1;
if exists (select 1 from testtable where rowval in (10,4))
set @UseThree = 0;

select COUNT(*) 
from testtable
where (@UseThree = 1 AND rowval=3)
    OR
    (@UseThree = 0 AND rowval in (10,4))

Upvotes: 1

Related Questions