SQL queries - filtering in a one to many relationship

user_3764619 0
Viewed 41

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?

sql
3 Answers

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)

user_69961500

This might help

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

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
                )
user_89493650
Related