CODEWITHSUNDEEP
sqlt-sql
Heejin
Heejin

Reputation: 4571

Select items that do not meet conditions

There are two tables JOB and WORKER.

JOB table

WORKER table

With these two tables, I want to find a list of jobs that no workers are now assigned. I made the following query, but it seems inefficient and verbose because of aggregate function SUM and CASE WHEN.

Any query better than this?

SELECT
    SUBQUERY.JOBID
FROM
(
    SELECT
        JOBID,
        SUM
        (
            CASE WHEN
                VACATION = 'N'
            THEN 1
            ELSE 0
            END
        ) NUM_WORKERS
    FROM
        JOB
    LEFT JOIN
        WORKER
    ON
        JOB.JOBID = WORKER.JOBID
    GROUP BY
        JOB.JOBID
) SUBQUERY
WHERE
    SUBQUERY.NUM_WORKERS = 0

Upvotes: 0

Views: 92

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

I would use this:

SELECT j.jobid 
FROM job AS j
WHERE NOT EXISTS
      ( SELECT 1
        FROM worker AS w 
        WHERE w.vacation = 'N'
          AND w.jobid = j.jobid
      ) ;

or this:

SELECT j.jobid 
FROM job AS j

EXCEPT

SELECT w.jobid
FROM worker AS w 
WHERE w.vacation = 'N' ;

Upvotes: 0

BeNdErR
BeNdErR

Reputation: 17927

Have a look here, hope it helps

SELECT JOBID 
FROM JOB 
WHERE JOBID NOT IN (
    SELECT j.JOBID 
    FROM JOB j 
    JOIN WORKER w ON j.JOBID = w.JOBID
    WHERE w.VACATION = 'N'
    GROUP BY j.JOBID
)

Sqlfiddle example: EXAMPLE

Upvotes: 1

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

Select * From job Where jobid not in( select distinct jobid from worker)

Thats it.

Upvotes: 1

Related Questions