CODEWITHSUNDEEP
sqlwhere-clause
Leo
Leo

Reputation: 4447

SQL/Rails find records where zero associated records are in a given state

I need to find all the records that have no associated records in any of a number of states.

I.E.:

Projects have a number of connected People.

Each person can be in one of a number of different states in relation to the Project (interested, participating, left, kicked, complaining, finished etc etc).

So to find a list of projects with 'no one working on them', I need to check all the people related to a project and make sure they are all in certain states. N.B. This means I'm really only interested in the People-Project join table.

Here is where I've got to (based on SQL: Select records where ALL joined records satisfy some condition):

SELECT * FROM projects
WHERE NOT EXISTS (
  SELECT NULL FROM people_projects AS pp
  WHERE pp.project_id = projects.id AND (
    pp.state = 'interested' OR
    pp.state = 'left' OR
    pp.state = 'kicked'
  )

This seems to work to some extent, i.e. it returns something, and not everything. However, some records returned definitely have related people_projects entries which are in one of the disallowed states, and there's at least one record I've found with no people_projects in any of the states which is not returned.

Any advice much appreciated.

N.B. This is actually being built through ActiveRecord in a Rails 3 project, but essentially I'm just writing the WHERE clause from scratch. Completely different approaches welcome! For now, I am doing this filtering post-query in Ruby...

Upvotes: 0

Views: 126

Answers (1)

Bort
Bort

Reputation: 7638

Does this work for you? Similar to the Exists

SELECT * FROM projects
WHERE id NOT IN (
    SELECT project_id
    FROM people_projects
    WHERE state IN ('interested', 'left', 'kicked')
)

Upvotes: 1

Related Questions