Question 236416 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    Many ways to approach this, a couple:

    Use a windowed aggregate:

    select users, files, file_age
from (
  select *, Min(file_age) over(partition by Users) MinAge
  from t
)t
where MinAge > 10;

    Use an aggregate in a correlated query:

    select * 
from t
where exists (
  select * from t t2
  where t2.users = t.users
  having Min(file_age) > 10
);
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search