CODEWITHSUNDEEP
sqlsql-server
Rob
Rob

Reputation: 59

Removing rows based on other rows and merging the remaining rows

I am having some trouble trying to get a table to show only the information that I want. I am trying to get the below table to only show ID's which have no true statements (1's) for either column 1 or column 2

IDs     | Column2 | Column3
--------+---------+--------
101     |    0    |    0 
101     |    0    |    1 
102     |    0    |    0
102     |    0    |    0
102     |    0    |    1
103     |    0    |    0
103     |    0    |    0
103     |    0    |    0
104     |    0    |    0
104     |    1    |    0

Ideally, the above table would be reduced to this:

Column1 | Column2 | Column3
--------+---------+--------
103     |    0    |    0

As ID 103 never shows a 1 for either column. Unfortunately I cant wrap my head around the logic. So far I have

SELECT DISTINCT
    IDs,
    Column1,
    Column2
FROM Table1
WHERE (Column1 <> 1 AND Column2 <> 1)

but this also shows all the rows from the other IDs that do not have a 1 e.g.

IDs     | Column2 | Column3
--------+---------+--------
101     |    0    |    0 
102     |    0    |    0
102     |    0    |    0
103     |    0    |    0
103     |    0    |    0
103     |    0    |    0
104     |    0    |    0

Is there a way of removing the rows when the ID has another row with a 1 in it, and then merging the remaining rows into single rows based on their ID?

Thanks Rob

Upvotes: 2

Views: 43

Answers (3)

kiran gadhe
kiran gadhe

Reputation: 743

I think this should resolve your question

SELECT * 
FROM   input_table 
WHERE  id IN (SELECT Max(id) 
              FROM   input_table 
              GROUP  BY id 
              HAVING Sum(column2) < 1 
                     AND Sum(column3) < 1);

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can also use not exists instead :

select distinct t.*
from table t
where not exists (select 1 from table t1 where t1.ids = t.ids and t1.column2 = 1) and
      not exists (select 1 from table t2 where t2.ids = t.ids and t2.column3 = 1);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think this does what you want:

select id, max(column2), max(column3)
from t
group by id
having max(column2) = 0 and max(column3) = 0;

Upvotes: 1

Related Questions