How to select a group of columns related to another column in MySQL?

Question

I have a MySQL table containing users, files and file ages as shown below.

Users	Files	File age
A	1	42
A	2	17
A	3	4
B	4	85
B	5	73
B	6	11
C	7	6
C	8	9
C	9	3

Now I need to select the names of all files of a specific user having an age larger 10. Users with no or only some files with an age > 10 shall not be selected.

With the example from above the result should be:

Users	Files	File age
B	4	85
B	5	73
B	6	11

I assume that the SQL ALL operator together with a sub query is my friend, but I do not get satisfying results here. Would be therefore happy to hear some ideas.

Thanks a lot!

Ben

Answer 1

There are several options.

One possibility is a left join and then check for null:

SELECT t1.*
FROM mytable t1 LEFT OUTER JOIN mytable t2
  ON t1.users = t2.users AND t2.fileage <= 10
WHERE t2.fileage IS NULL

Another possibility is using not exists:

SELECT *
FROM mytable t1
WHERE NOT EXISTS
 (
  SELECT *
  FROM mytable t2
  WHERE t1.users = t2.users AND t2.fileage <= 10
 )

Another possibility is using < ALL (as you mentioned in the question):

SELECT *
FROM mytable t1
WHERE 10 <= ALL
 (
  SELECT fileage
  FROM mytable t2
  WHERE t2.users = t1.users
 )

You can see a Fiddle here.

I've edited my answer to use <= instead of just < since I had your boundary condition wrong.