mysql中至少有一个如何表示,mysql如何查找交叉引用表中至少有一行为空或条件...

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

解决方案

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'