SQL queries - filtering in a one to many relationship-CodePudding

I am writing a large SQL query and have narrowed down the problem to the following (simple example).

id	status
1	-1
2	1
3	-1
3	1

Now I want to filter out all ids which never have a status of 1. In this example, I only want to return id 1 since id 3 both can have status 1 and -1. How can I do that?

CodePudding user response：

Not tested but this should do the trick.

select *
from yourTable T
where not exists(
  select 1 
  from yourTable X 
  where X.id = T.id 
  and X.status = 1
)

(sorry, edited: you don't want the records having -1 but 1)

CodePudding user response：

This might help

select id from my_table
group by id
having sum(status) = -count(id)

CodePudding user response：

You can use sub query to filter:

SELECT * 
FROM YourTable YT 
WHERE id NOT IN (
                SELECT DISTINCT X.id FROM YourTable X WHERE X.status = 1
                )