title: Converting PostgreSQL Subqueries into Joins
body: <p>Below is an example schema with 3 tables.  I'm trying to run a query that returns all Jobs where all child Shifts are of status 6.  If a Job has a child Shift with a status of 5, the Job should not be returned.  The proper response for a query from the sample data inserted below is no rows returned.</p>
  
  <p>There is a working query below with the comment "Works".  I am trying to refactor the "works" query to use joins instead of subqueries.  The query with the comment "Does not work" is my attempt.  </p>
  
  <pre><code>-- begin setup and table creation: only run this section once.
  
  CREATE EXTENSION "uuid-ossp";
  
  CREATE TABLE jobs
  (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    CONSTRAINT jobs_pkey PRIMARY KEY (id)
  );
  
  CREATE TABLE bookings
  (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    job_id uuid,
    CONSTRAINT bookings_pkey PRIMARY KEY (id)
  );
  
  CREATE TABLE shifts
  (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    booking_id uuid,
    status integer,
    CONSTRAINT shifts_pkey PRIMARY KEY (id)
  );
  
  insert into jobs (id) values ('e857c86c-bc31-11e6-9aae-57793f585d49');
  
  insert into bookings (id, job_id) values ('736da82c-bc32-11e6-b9b8-f36753d321ac', 'e857c86c-bc31-11e6-9aae-57793f585d49');
  insert into bookings (id, job_id) values ('7d839e5c-bc32-11e6-8bb3-4fa95be86a74', 'e857c86c-bc31-11e6-9aae-57793f585d49');
  
  insert into shifts (booking_id, status) values ('736da82c-bc32-11e6-b9b8-f36753d321ac', 6);
  insert into shifts (booking_id, status) values ('7d839e5c-bc32-11e6-8bb3-4fa95be86a74', 5);
  
  -- end setup and table creation
  </code></pre>
  
  <p>We want all jobs where all child shifts are of status 6.  If a job has a child shift with a status of 5, the job should not be returned.  The proper response for a query from the sample data inserted above is no rows returned.</p>
  
  <p>Does not work :(</p>
  
  <pre><code>SELECT "jobs".* 
  FROM "jobs" 
     inner join bookings b1 on jobs.id = b1.job_id 
     inner join shifts s1 on b1.id = s1.booking_id 
     left outer join bookings b2 on jobs.id = b2.job_id 
     left outer join shifts s2 on b2.id = s2.booking_id and s2.status IN (2,3,4,5) 
  WHERE s1.status = 6 
    AND s2.id IS NULL 
  GROUP BY "jobs"."id";
  </code></pre>
  
  <p>Works</p>
  
  <pre><code>SELECT "jobs".*
  FROM "jobs"
  WHERE jobs.id IN (
      SELECT job_id
      FROM bookings
      WHERE bookings.id IN (
          SELECT booking_id FROM shifts WHERE status = 6
      )
  ) AND jobs.id NOT IN (
      SELECT job_id FROM bookings WHERE bookings.id IN (
          SELECT booking_id FROM shifts WHERE status IN (2,3,4,5)
      )
  )
  GROUP BY "jobs"."id";
  </code></pre>
  
  <p>How can I refactor the "works" query to use joins instead of subqueries?  The "does not work" query is my attempt.  </p>