i am novice at SQL and have problems hope you can help me :
mysql5
table TRAINING_REQUESTS
+------------+--------+ | ID_TR | FIELD | +------------+--------+ | ... | .. | | 254 | .. | | ... | .. | | 286 | .. | | ... | .. | | 401 | .. | | ... | .. | | 405 | .. | | 406 | .. | | ... | .. | +------------+--------+
table PLANNING_REQUESTS
+----------+----------+----------+ | ID_PR | ID_TR | 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 | | ... | ... | ... | +----------+----------+----------+
I needs 3 things :
Expected result
Constraint : all the training_requests (ID_TR) that are not ok , it means (>> ALL TRAINING REQUESTS(ID_TR) that have ALL ASSIGNED PLANNING REQUESTS(ID_PR) WITH TRAINER EQUALS (NULL OR FREE)
+------------+-------+ | ID_TR | field | +------------+-------+ | 254 | .. | | 286 | .. | +------------+-------+
Constraint : all the training_requests (ID_TR) that are nearly ok, it means (>> ALL TRAINING REQUESTS(ID_TR) that have ALL ASSIGNED PLANNING REQUESTS(ID_PR) WITH TRAINER AT LEAST ONE TIME DIFFERENT FROM (NULL OR FREE) AND NOT ALL WITH TRAINER ASSIGNED (DIFFERENT FROM NULL OR FREE)
+------------+-------+ | ID_TR | FIELD | +------------+-------+ | 405 | .. | +------------+-------+
Constraint : all the training_requests that are not ok and nearly ok
+------------+------+ | ID_TR |FIELD | +------------+------+ | 405 | .. | | 254 | .. | | 286 | .. | +------------+------+
thx for all !
SELECT tr.ID_TR,tr.field
FROM planning_requests pr
INNER JOIN training_requests tr
ON tr.ID_TR = pr.ID_TR
WHERE pr.ID_TR NOT IN
(
SELECT cpr.ID_TR
FROM planning_requests cpr
WHERE trainer IS NOT NULL AND trainer <> 'FREE'
)
GROUP BY ID_TR