CODEWITHSUNDEEP
sqlsql-server
asinha
asinha

Reputation: 337

SQL query to get data with condition

I am using a SQL Server database.

A | B | C
--+---+---
1 |11 |  0
1 |12 |  0
1 |13 |  0
2 |33 |  5
2 |34 | 10
2 |35 | 78
5 |45 |  0
5 |49 |  0
5 |51 |  1
8 |10 |  0
8 |14 |  2
8 |34 |  3

I am looking for sql query to fetch distinct A value which is having at least one value of C is zero but should not all the values would be zero. In the above table I should get 5 & 8

At the moment for A value which is having all values are zero , I am doing like as below so something similar would be helpful

SUM(ABS(table.C)) = 0

Upvotes: 0

Views: 2641

Answers (4)

Paul Spiegel
Paul Spiegel

Reputation: 31832

If C is never negative:

select A
from mytable
group by A
having min(C) = 0
   and max(C) > 0

db<>fiddle

If C can be negative:

select A
from mytable
group by A
having min(abs(C)) = 0
   and max(abs(C)) > 0

db<>fiddle

And there are many other ways:

select distinct t0.A
from mytable t0
join mytable t1 on t1.A = t0.A
where t0.C = 0
  and t1.C <> 0;

select distinct t0.A
from mytable t0
where t0.C = 0
  and exists (
    select *
    from mytable t1
    where t1.A = t0.A
      and t1.C <> 0
  );

Upvotes: 2

The Impaler
The Impaler

Reputation: 48865

You can try:

select distinct a
from my_table t
where t.a in (select x.a from my_table x where x.c = 0)
  and t.a in (select x.a from my_table x where x.c <> 0)

Upvotes: 0

KeithL
KeithL

Reputation: 5594

I think this will work:

;with zeros as
(
select distinct colA
from table
where ColC=0
)

select distinct colA
from table
   join zeros on table.ColA=zeros.ColA
where table.ColC <> 0

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

try like below by using exists

select A from table
where exists (select 1 from table t2 where t1.A=t2.A
                  and t2.c=0)
 group by A
 having sum(c)<>0

Upvotes: 0

Related Questions