1

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?

Casper Lindberg
  • 600
  • 1
  • 6
  • 21

3 Answers3

2

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)

johey
  • 1,139
  • 1
  • 9
  • 25
2

This might help

select id from my_table
group by id
having sum(status) = -count(id)
long_hair_programmer
  • 510
  • 5
  • 8
  • 1
    Clever solution; though this only returns the id; not full row(s) so you could just use `having count(case when status = 1 then 1 end) > 0`, which would be cleaner as it doesn't rely on specific values other than the `status=1` given in the requirements. i.e. The solution in this answer works because the only status other than `1` is `-1` & that's the only status with a negative value. If a new status were added that should also be included/excluded, results may be unexpected. – JohnLBevan Sep 02 '22 at 09:44
1

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
                )
Ananth MK
  • 392
  • 1
  • 5
  • Nice solution; though [johey's](https://stackoverflow.com/a/73580573/361842) is potentially more efficient (more info https://stackoverflow.com/a/11074428/361842) – JohnLBevan Sep 02 '22 at 09:50