mysql how to find if at least one row from cross reference table is null or criteria

Question

i have trouble with mysql, i dont find the way to do it maybe i dont know the good mysql keyword

mysql5

+----------+------------+----------+
| ID       | FOREIGNKEY |  TRAINER |
+----------+------------+----------+
|      ... | ...        | ...      |
|      475 | 254        |  NULL    |
|      476 | 254        |  NULL    |
|      477 | 254        |  NULL    |
|      478 | 286        |  NULL    |
|      479 | 286        |  FREE    |
|      480 | 286        |  FREE    |
|      481 | 401        |  FREE    |
|      482 | 401        |  1       |
|      483 | 401        |  FREE    |
|      484 | 405        |  NULL   |
|      485 | 405        |  1       |
|      486 | 405        |  5       |
|      487 | 405        |  FREE    |
|      488 | 406        |  1       |
|      489 | 406        |  5       |
|      490 | 406        |  5       |
|      491 | 406        |  2       |
|      ... | ...        |  ...     |
+----------+------------+----------+

Expected result

Constraint :

i would like to get all the foreignkey id that have not all trainer NULL or FREE (at least 1 but can be 2 or more) but at least one should be NULL

+------------+-------+
|    ID_TR   | FIELD |
+------------+-------+
|      405   |   ..  |
+------------+-------+

i dont know how to do it in mysql ? Group then HAVING one trainer == FREE OR NULL ?

thanks for helping me

Answer 1

This sounds like a classic usecase for the EXISTS operator:

SELECT *
FROM   mytable a
WHERE  EXISTS (SELECT 1
               FROM   mytable b
               WHERE  a.foreignkey = b.foreignkey 
               AND    trainer IS NOT NULL 
               AND    trainer <> 'FREE'

EDIT:
If you just just want the distinct different foreignkeys:

SELECT DISTINCT foreignkey
FROM   mytable a
WHERE  EXISTS (SELECT 1
               FROM   mytable b
               WHERE  a.foreignkey = b.foreignkey 
               AND    trainer IS NOT NULL 
               AND    trainer <> 'FREE'