SQL Null value & join where not exist on other table-CodePudding

I have these 2 tables

 -------------------     ----------------------------------------------    
|      movies       |   |                   ratings                    |  
 -------------------     ----------------------------------------------    
| Id   |    Name    |   | Id_movie   | id_user | id_rating | user_rate |
|-------------------|   |----------------------------------------------| 
| 1    |  movie 1   |   | 1          |   20    |   1       |     5     |
| 2    |  movie 2   |   | 1          |   21    |   2       |     3     |
| 3    |  movie 3   |   | 1          |   22    |   3       |     4     |
 -------------------    | 2          |   21    |   3       |     5     |
                        | 2          |   22    |   3       |     4     |
                        | 3          |   22    |   3       |     5     |
                         ----------------------------------------------

i want to get

 ----------------------------------------    
|                movies                  | 
 ----------------------------------------   
| Id_user   |    id_movie    |  Name     |
|----------------------------------------| 
| 20        |        2       |  movie 2  |
| 20        |        3       |  movie 3  |
 ----------------------------------------

condition where user = 20 didnt rate movie 2 & 3. is it possible?

Can somebody help me? thank you

CodePudding user response：

You must generate all combinations user-movie then test each combination for its absence in ratings.

SELECT user.user id_user, 
       movies.id id_movie, 
       movies.name
FROM ( SELECT 20 AS user ) AS user    -- needed user(s)
                                      -- for all users use 
                                      -- ( SELECT DISTINCT user FROM ratings ) AS user                                   
CROSS JOIN movies                     -- cross join generates all combinations
WHERE NOT EXISTS ( SELECT NULL        -- select only non-existent combinations
                   FROM ratings
                   WHERE ratings.id_user = user.user
                     AND ratings.id_movie = movies.id )