dimanche 19 juin 2016

sql - how to get courses with module=5

I'm trying to get all courses which has only "module=5", any different course. As you can see below I have 4 courses, such as, course 2, 3, 4, 5. But just two of them has only "module=5", e.g. course 2, 5.

+----+--------+--------+
| id | course | module |
+----+--------+--------+
|  1 |      2 |      5 |
|  2 |      3 |      5 |
|  3 |      3 |     11 |
|  4 |      4 |      5 |
|  5 |      4 |      3 |
|  6 |      5 |      5 |
|  7 |      4 |      6 |
|  8 |      4 |      5 |
+----+--------+--------+

I've tryed do two queries, in the first I return all courses which has module=5 and in the second I return all courses which has module!=5, then I save in 2 files and execute the unix command diff to see the difference between both files.

  1. save in a file all courses which has module=5:

    SELECT DISTINCT fullname 
    FROM            mdl_course 
    LEFT JOIN       mdl_course_modules 
    ON              mdl_course.id=mdl_course_modules.course 
    WHERE           mdl_course_modules.module=5 
    into            outfile '/tmp/forum';
    
  2. save in file all courses which has module!=5:

    SELECT DISTINCT fullname 
    FROM            mdl_course 
    LEFT JOIN       mdl_course_modules 
    ON              mdl_course.id=mdl_course_modules.course 
    WHERE           mdl_course_modules.module!=5 
    into            outfile '/tmp/plus_forum';
    
  3. Then, execute the difference:

    $ diff forum plus_forum
    

But I'd like to return all courses which has only module=5 in only one query. Is it possible?

Aucun commentaire:

Enregistrer un commentaire