how to query sql cross-reference table

Question

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 !

ofc i tried but im beginner at sql and i dont know how to query cross reference table — juhnz, Jan 25 '14 at 22:39
You have very basic tasks here. Quite suitable for beginners. — PM 77-1, Jan 25 '14 at 22:48
and how can i have both request in only one ? all the training_requests that are not ok and nearly ok should i use union ? — juhnz, Jan 26 '14 at 10:10
SELECT * FROM training_requests tr JOIN planning_requests pr ON tr.ID_TR = pr.ID_TR WHERE EXISTS (SELECT * FROM planning_requests pr WHERE pr.trainer IS NULL) doesnt works — juhnz, Jan 26 '14 at 10:24
I tried EXISTS JOIN AND SUBQUERIES but it doesnt works : SELECT * FROM training_requests tr JOIN planning_requests pr ON tr.ID_TR = pr.ID_TR WHERE EXISTS (SELECT * FROM planning_requests pr WHERE pr.trainer IS NULL) i would like to check if a group of planning request for the SAME training request is NULL not only one — juhnz, Jan 26 '14 at 10:25
i found the first needs: >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 but now i need with at least one different from NULL — juhnz, Jan 26 '14 at 11:45
Edit your question. Don't post updates as answers (unless you actually solve your issue and would like to post the answer you discovered). — David Makogon, Jan 26 '14 at 22:41
Have you looked at my answer yet? Click on Fiddle link and you can see how it works. Obviously there are other solutions as well. — PM 77-1, Jan 26 '14 at 22:48

score 0 · Answer 1 · answered Jan 26 '14 at 22:17

It can be done the following (possibly inefficient) way:

#1 (not OK)

SELECT tr.*
FROM TRAINING_REQUESTS tr
JOIN PLANNING_REQUESTS pr ON tr.id_tr = pr.id_tr
GROUP BY pr.id_tr
HAVING SUM(CASE WHEN pr.trainer IS NULL or pr.trainer = 'FREE' THEN 1 ELSE 0 END) = COUNT(*)
;

#2 (nearly OK)

SELECT tr.*
FROM TRAINING_REQUESTS tr
WHERE EXISTS (SELECT 1 
              FROM PLANNING_REQUESTS pr 
              WHERE tr.id_tr = pr.id_tr 
              AND pr.trainer IS NOT NULL AND pr.trainer <> 'FREE') 
  AND EXISTS (SELECT 1 
              FROM PLANNING_REQUESTS pr 
              WHERE tr.id_tr = pr.id_tr 
              AND (pr.trainer IS NULL OR pr.trainer = 'FREE'))        
;

#3

    SELECT tr.*
    FROM TRAINING_REQUESTS tr
    WHERE EXISTS (SELECT 1 
                  FROM PLANNING_REQUESTS pr 
                  WHERE tr.id_tr = pr.id_tr 
                  AND (pr.trainer IS NULL OR pr.trainer = 'FREE'))        
;

Here's SQL Fiddle with the results.

Please note that my results in #2 (and, therefore, #3) are different from yours as they include 401.

score 0 · Accepted Answer · edited Apr 10 '15 at 17:05

0

    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

edited Apr 10 '15 at 17:05

Dan

5,153
4
31
42

answered Jan 27 '14 at 00:02

juhnz

304
3
11

how to query sql cross-reference table

2 Answers2