CODEWITHSUNDEEP
sql
Alexey Abraham
Alexey Abraham

Reputation: 398

SQL selecting only rows from 2nd table, containing exact results received from 1st table

The answer must be rather simple, but I stuck with it for a while. 2 tables

  1. BUILDING-TYPE Some types of work are located in some buildings

  2. WORKER-TYPE Worker can do some types of work

TYPE_BLD_ID BLD TYPE
2 1 2
6 2 5
7 2 6
8 3 6
9 4 2
4 5 3
3 5 4
5 5 7
13 6 1
14 6 7
12 7 5
WRK_TYPE_ID WORKER TYPE
0 0 0
5 1 3
4 1 4
6 1 7
7 2 2
11 2 4
10 2 5
9 2 6
12 2 7
15 3 5
14 3 6
19 3 7
17 4 1
16 4 2
18 4 7

WHEN I select e.g. Building #4, I expect getting Worker #1, who can handle types of work 1&7. It looks rather stupidly simple, but

  1. I select types of work from the building: SELECT TYPE FROM TYPES_IN_BUILDING WHERE BLD = 1; Get 1 and 7
  2. NEXT I do SELECT WORKER FROM WORKER_TYPES WHERE TYPE IN (1,7);

And, obviously receive all workers, who can do 1 and 7. But I want to get ONLY WORKERS, who has BOTH types, 1 AND 7.

The issue in the question - JOINs in the SQL version, used in my system, something extremely outdated.

THE FOLLOWING WUERY DID NOT WORK:

'''

SELECT * FROM WORKER W
WHERE W.WORKER IN (SELECT WA.WORKER
    FROM WORKER_ATYPE WA
    WHERE WA.ATYPE IN 
    (SELECT AB.ATYPE FROM ATYPE_IN_BUILD AB WHERE AB.BLD = 6)
    GROUP BY WA.WORKER
    HAVING COUNT(DISTINCT ATYPE) = 
        (SELECT COUNT(ATYPE) FROM TYPE_IN_BUILD WHERE BLD = 6)
 ;

'''

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You want the worker types to be a superset of the building types. You can do this using:

select w.worker
from (select b.*, count(*) over (partition by building) as cnt
      from building b
     ) b join
     workers w
     on b.type = w.type
where b.building = 1
group by w.worker, b.cnt
having count(*) = b.cnt;

This matches workers to the building based on types. Only rows that match are included. Then, the workers are aggregated and a worker matches if the total number of rows matches the number of types for the building.

Upvotes: 1

Related Questions