-1

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

juhnz
  • 304
  • 3
  • 11

3 Answers3

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'
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • exists looks good but it returns 15 rows , i think there is a problem – juhnz Jan 26 '14 at 13:13
  • After re-looking at the OP, I think my edit should answer the question. If not, please elaborate why this differs from the output you need. Thanks! – Mureinik Jan 26 '14 at 13:16
  • sorry i got 286, 401, 405, 406 and i only need 405 because he has one row with NULL and one different FROM NULL OR FREE – juhnz Jan 26 '14 at 13:20
  • should i use another exists for at least one null row ? – juhnz Jan 26 '14 at 13:27
  • i tried : SELECT DISTINCT id_tr FROM planning_requests a WHERE EXISTS (SELECT 1 FROM planning_requests b WHERE a.id_tr = b.id_tr AND trainer IS NOT NULL AND trainer <> 'FREE' ) AND EXISTS (SELECT 1 FROM planning_requests b WHERE a.id_tr = b.id_tr AND trainer IS NULL ) but i got 286 and 405, it isnt good – juhnz Jan 26 '14 at 13:38
  • hum finally double exists looks working i had FREE2 in one ROW ... do you think its a good solution ? – juhnz Jan 26 '14 at 13:48
  • Why do you need the double `EXISTS`? If you have a `FREE2` entry you should either fix it, or add and additional clause `AND trainer <> 'FREE2'` – Mureinik Jan 26 '14 at 13:51
  • yes i fixed it to works but i need double exists for the other one condition that is at least one NULL – juhnz Jan 26 '14 at 14:01
0
SELECT   t.*
FROM     my_table    t
    JOIN cross_table x ON x.FOREIGNKEY = t.ID_TR
WHERE    x.TRAINER IS NOT NULL
     AND x.TRAINER <> 'FREE'
GROUP BY t.ID_TR
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • :-( I don't like to see GROUP BY used this way, not even by gurus – Strawberry Jan 26 '14 at 13:07
  • @Strawberry: What about it don't you like? The selection of non-aggregated columns in MySQL is a *deliberate* feature that gives a significant performance boost... – eggyal Jan 26 '14 at 13:11
  • @juhnz: From your sample data, I'd expect to see IDs 401, 405 and 406 in the results (they all have at least one record whose `TRAINER` column is neither `NULL` nor `'FREE'`). Is this not what you were after? – eggyal Jan 26 '14 at 13:13
  • I admit that it's an aesthetic dislike - although 'significant'? Are you sure? – Strawberry Jan 26 '14 at 13:16
  • oh well sorry i means 1 column is neither NULL OR FREE + 1 column at least NULL – juhnz Jan 26 '14 at 13:16
0

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

select foreignkey,
       sum(trainer is null) as NumNulls,
       sum(trainer = 'Free') as NumFrees,
       count(*) as Num
from table t
group by 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 > 0 and NumFrees > 0

or perhaps this:

having NumNulls > 0 and NumFrees > 0 and cnt >= 2;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • after modifying a bit your requests it seems working but what is better for big table having or exists ? select id_tr from planning_requests t group by id_tr having sum(trainer is NULL) > 0 and sum(trainer <> 'FREE' AND trainer IS NOT NULL) > 0 and what do you use to format SQL code on stackoverflow ? – juhnz Jan 26 '14 at 14:04
  • @juhnz . . . If you have indexes on the table, then you can probably optimize `exists` to have better performance. Aggregation and `having` are more flexible for describing different possible conditions. – Gordon Linoff Jan 26 '14 at 14:05
  • ok then i think i will use exists because having looks to slow atm. what do you use for SQL Formatting on stack overflow ? thx – juhnz Jan 26 '14 at 14:12
  • @juhnz . . . I have my own style of writing SQL. I format it myself. – Gordon Linoff Jan 26 '14 at 14:20