ALL operator clause in SQL query

Question

I have this table schema.

Machine(machine_id, size)
Operator(operator_id, name)
OperationLog(machine_id, operator_id, date, comment)
  machine_id: FK(Machine)
  operator_id: FK(Operator)

Assuming I want a query that only gives me the name of the operators that operated all machines with size above 5m2

Would using the ALL operator give me the desired result? As in, from the list of Machines with size above 5, Operators that have Logs must match all of those machines.

SELECT O.name
  FROM Operator O NATURAL JOIN OperationLog L
  WHERE L.machine_id = ALL (
      SELECT M.machine_id     
      FROM Machine M  
      WHERE size >5);

Or would I need to do a "double negation" like so?

SELECT O.name
FROM Operator O
WHERE NOT EXISTS(
   SELECT M.machine_id
   FROM Machine M 
   EXCEPT
   SELECT L.machine_id            
   FROM OperationLog L NATURAL JOIN Machine M      
   WHERE L.operator_id = O.operator_id
         AND size >5);

I feel I am complicating too much and there's probably a better way.

It sounds like you're looking for relational division. That term plus mysql should find you plenty of examples and approaches. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 2, 2017 at 11:28
Add some sample table data and the expected result. (As well formatted text.) — jarlh
– jarlh, Commented Feb 2, 2017 at 11:28
Your ALL subquery results in true when it returns only a single machine_id (one or multiple rows). — dnoeth
– dnoeth, Commented Feb 2, 2017 at 11:33
for each operator, count the number of unique machines he operated whose size is above 5, and compare it to the number of machines on Machine whose size is above 5, return only operators with those numbers equal — mounaim
– mounaim, Commented Feb 2, 2017 at 11:34

Serge · Accepted Answer · 2017-02-02 11:34:27Z

2

Count how many different machines of (size >= 5) each operator used and compare that number to the total number of such machines:

SELECT op.name
FROM operator op
    INNER JOIN operationlog l
        ON op.operator_id = l.operator_id
    INNER JOIN machine m
        ON l.machine_id
WHERE m.size >= 5
GROUP BY op.name
HAVING COUNT(DISTINCT m.machine_id) = (
        SELECT COUNT(*)
        FROM machine
        WHERE size >= 5
)

answered Feb 2, 2017 at 11:34

Serge

4,0662 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-02-02 12:42:07Z

2

I am a fan of using group by and having for this purpose:

SELECT O.name
FROM Operator O JOIN
     OperationLog L
     ON L.operator_id = O.operator_id JOIN
     Machine M 
     ON L.machine_id = M.machine_id 
WHERE M.size > 5
GROUP BY O.name  
HAVING COUNT(DISTINCT M.machine_id) = (SELECT COUNT(DISTINCT M2.machine_id)
                                       FROM Machine M2  
                                       WHERE size > 5
                                      );

If there are not duplicates in the tables, then use COUNT(*) rather than COUNT(DISTINCT).

I should note that I strongly discourage you from using NATURAL JOIN. It is a bug waiting to happen. Why? It simply uses columns that have the same names. It doesn't even use the same types. For instance, most tables I create have columns for CreatedBy and CreatedAt and it would use these columns.

edited Feb 2, 2017 at 12:42

answered Feb 2, 2017 at 11:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Serge Over a year ago

machine_id is probably PK in the machine table. If so, COUNT(*) will be suffice in the sub-query (instead of COUNT(DISTINCT M2.machine_id). Apart from that, our queries are identical :).

Collectives™ on Stack Overflow

ALL operator clause in SQL query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related