0

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...

Community
  • 1
  • 1
Leo
  • 4,217
  • 4
  • 25
  • 41

1 Answers1

1

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')
)
Bort
  • 7,398
  • 3
  • 33
  • 48
  • Sorry for delay in getting back to you @Bort. I've just been playing around with this, and it seems to work perfectly, even when expanded to include additional criteria in the joined table. It's also less verbose. Perfect! Many thanks. – Leo Jan 25 '12 at 14:27