Mysql How To Find If At Least One Row From Cross Reference Table Is Null Or Criteria

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 | +

Solution 1:

This sounds like a classic usecase for the EXISTS operator:

SELECT*FROM   mytable a
WHEREEXISTS (SELECT1FROM   mytable b
               WHERE  a.foreignkey = b.foreignkey 
               AND    trainer ISNOTNULLAND    trainer <>'FREE'

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

SELECTDISTINCT foreignkey
FROM   mytable a
WHEREEXISTS (SELECT1FROM   mytable b
               WHERE  a.foreignkey = b.foreignkey 
               AND    trainer ISNOTNULLAND    trainer <>'FREE'

Solution 2:

SELECT   t.*
FROM     my_table    t
    JOIN cross_table x ON x.FOREIGNKEY = t.ID_TR
WHERE    x.TRAINER ISNOT NULL
     AND x.TRAINER <> 'FREE'GROUPBY t.ID_TR

Solution 3:

You can count the number that are 'FREE' and NULL and then do logic:

select foreignkey,
       sum(trainer isnull) as NumNulls,
       sum(trainer = 'Free') as NumFrees,
       count(*) as Num
from table t
groupby foreignkey

You then want to add a having clause to get what you want. I am not sure exactly what this means: "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".

For instance, this might be what you want:

having NumNulls >0and NumFrees >0

or perhaps this:

having NumNulls >0and NumFrees >0and cnt >=2;