How to display which user not mapped course table in courseusermapping table

Question

I have a Course Table which is common for all user to select their related course and saved them in CourseUserMapping Table. Now my requirement is i want to display all courses which user not selected in course Table and hide selected courses list.

    select c.* from Course c full outer join CourseUserMapping cum on c.CID = cum.CID 
where c.CID is NULL Or cum.CID is NULL and cum.UserID='u3' 

the above query is not working as i expected.

For better understand i created a picture

enter image description here


Show source
| sql-server   | sql-server-2008   | sql   | sql-server-2005   2017-01-01 10:01 1 Answers

Answers ( 1 )

  1. 2017-01-01 10:01

    Change the join to a LEFT JOIN and modify the WHERE clause:

    select c.*
    from Course c
    left join CourseUserMapping cum
        on c.CID = cum.CID and
           cum.UserID = 'u3'
    where cum.CID is NULL
    

    The LEFT JOIN above will retain all courses, and then retain only those courses which did not map to any users. This is contained within the condition where cum.CID is NULL.

◀ Go back