Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Unanswered: Converting Subqueries into Joins

    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.

    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.

    Thanks!

    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
    
    -- 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.
    
    -- Does not work :(
    --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";
    
    -- Works
    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";
    
    -- How can I refactor the "works" query to use joins instead of subqueries?  The "does not work" query is my attempt.  Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,993
    Provided Answers: 23
    There is a working query below with the comment "Works". I am trying to refactor the "works" query to use joins instead of subqueries
    Why do you want to do that?
    A join is not necessarily a functional equivalent of a sub-query (and if it is, the optimizer will most likely produce the same execution plan anyway)

    Is the current query too slow?

    If it is too slow, please read: http://wiki.postgresql.org/wiki/SlowQueryQuestions and then supply the missing information.
    Last edited by shammat; 12-07-16 at 04:21.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Aug 2017
    Posts
    13
    Provided Answers: 1

    You can use CASE to do the include/exclude

    Even if the current query performs the same it's a mess to read. Simplifying will make your job easier down the road.

    select jobs.id
    from jobs
    inner join bookings b1 on jobs.id = b1.job_id
    inner join shifts s1 on b1.id = s1.booking_id
    group by jobs.id
    having max(case when s1.status=6 then 1 else 0 end)=1
    and max(case when s1.status in (2,3,4,5) then 1 else 0 end)=0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •