Select certain value only

Question

I have 2 table, but I wanted to query the 'rejected' status only, means I need query the result that the user has only rejected status, instead of having approve & reject, or approve in submissions table

Users Table
-----------
id | name 
-----------
 1 | John
 2 | Doe
 3 | Testing
 4 | Sample



Submission Table
-------------------------------
id | user_id | title  | status
-------------------------------
 1 | 1       | title1 | approved
 2 | 1       | title2 | rejected
 3 | 2       | title3 | approved
 4 | 2       | title4 | approved
 5 | 3       | title5 | rejected
 6 | 3       | title6 | rejected
 7 | 3       | title7 | rejected
 8 | 4       | title8 | approved
 9 | 4       | title9 | approved
 10| 4       | title10| rejected
 11| 4       | title11| rejected

Below is the result I wanted to achieve :

But I outer join the result query by 'rejected' only but still have some 'approved' result by the users.

but with above query, I'd this result.

What I wanted to query is , query the submissions just have status 'rejected' only, fully ignore the 'approved' , 'approve or reject' result.

Answer 1

I found a solution already which is using WHERE NOT EXISTS to filter the approved result in submission

SELECT u.id AS user_id,s.*, u.name
FROM submissions s
LEFT OUTER JOIN users u
ON s.user_id = u.id

WHERE NOT EXISTS (
    SELECT USER_ID
    FROM submissions tmp
    WHERE tmp.User_ID = s.User_ID
    AND tmp.status = 'approved'
    )

AND STATUS = 'rejected'