CODEWITHSUNDEEP
sqlsql-servert-sql
Sca
Sca

Reputation: 195

How to select non zero rows in SQL Server

I am using MS SQL Server. I have 20 columns in total in my table out of which 10 are varchar, 10 are int. I have to fetch all the rows wherein I have any non-zero values in those 10 int rows ( say p1 to p10)

I have made the following query

select * from table_name where exists (select * from table_name where p1 > 0)

I want to select p1 to p10 columns with values greater than 0 ( in any of them ). This query returns all the rows. I am unable to find a way around this.

Upvotes: 0

Views: 5865

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

One more approach considering NULLIF. If equal to 0, we are considering it as null.

DECLARE @table table(p1 int, p2 int, p3 int, 
p4 int, p5 int,p6 int, p7 int,p8 int, p9 int, p10 int)

insert into @table
VALUES
(1,1,1,1,1,1,1,1,1,1),
(1,1,1,0,1,1,1,1,1,1),
(1,1,1,0,null,null,1,1,1,1),
(0,0,0,0,0,0,0,0,0,0);

SELECT * 
FROM
(
SELECT
NULLIF(p1,0) as p1
,NULLIF(p2,0) as p2
,NULLIF(p3,0) as p3
,NULLIF(p4,0) as p4
,NULLIF(p5,0) as p5
,NULLIF(p6,0) as p6
,NULLIF(p7,0) as p7
,NULLIF(p8,0) as p8
,NULLIF(p9,0) as p9
,NULLIF(p10,0) as p10
from @table) as t
WHERE p1 is not null 
or p2 is not null
or p3 is not null
or p4 is not null
or p5 is not null
or p6 is not null
or p7 is not null
or p8 is not null
or p9 is not null
or p10 is not null


+----+----+----+------+------+------+----+----+----+-----+
| p1 | p2 | p3 |  p4  |  p5  |  p6  | p7 | p8 | p9 | p10 |
+----+----+----+------+------+------+----+----+----+-----+
|  1 |  1 |  1 | 1    | 1    | 1    |  1 |  1 |  1 |   1 |
|  1 |  1 |  1 | NULL | 1    | 1    |  1 |  1 |  1 |   1 |
|  1 |  1 |  1 | NULL | NULL | NULL |  1 |  1 |  1 |   1 |
+----+----+----+------+------+------+----+----+----+-----+

Upvotes: 0

gotqn
gotqn

Reputation: 43636

Try this:

select * 
from table_name 
where p1 > 0 OR p2 > 0 OR ... OR p10 > 0

or

select * 
from table_name 
where COALESCE(p1, p2, ... ,p10) > 0

Also, if interested, your query is not working as you want, as you are telling the engine to do different thing. You are saying:

  1. give me all rows

    select * from table_name T1

  2. if the condition is true - and the condition is, is there a row in the table (and there is at least one row)

    where exists 
(
    select * from table_name T2 where T2.p1 > 0
)

if you want do use exists, you need to eval such condition for each row:

select * from table_name T1
where exists 
(
    select * 
    from table_name T2 
    where T2.p1 > 0
        AND T1.[id] = T2.[id]
)

but it's a kind of overkill in this case.

Upvotes: 2

Related Questions