find missing values in MySQL

Question

Hi all, I am trying to write a query to find out what users are not enrolled on some courses.

I have 2 tables; courses, and users. Both contain the fields 'id' and then 'coursename' and username' respectively.

Using these two table the existing system adds users to a third table (called enrolled_users). Users take up one row for each. For example if user id '1' is on 4 courses the enrolled_users looks like this:

-----------------------
| user_id | course_id |
-----------------------
|     1   |     1     |
|     1   |     2     |
|     1   |     3     |
|     1   |     4     |
-----------------------

As you can see this table data is not built on a relationship, it is generated in PHP using the two tables 'courses' and 'users'.

I am not sure how to write a query on how to find out that courses user '1' is not enrolled in, if there are say 10 courses, but we can see user '1' is only on courses 1-4.

Am I suppose to use something like a LIKE or NOT LIKE?

SELECT u.id, c.id, ec.user_id, ec.course_id
FROM users u, courses c, enrolled_courses ec
WHERE u.id = ec.user_id
AND c.id = ec.course_id
AND u.id = '1'

I have tried using != and <> but this doesn't show what i need; a list of courses the user is not enrolled on.

Sorry if I am being vague, trying to get my head round it!

Using MySQL 5.0, on an existing system I cannot modify, only query from (for the moment).

Answer 1

Just use a not in and a sub select.

select * from courses c where c.id not in 
   (select  course_id  from enrolled_courses  where  user_id='1') 

Answer 2

If you are looking for a specific single user ID, be sure to include that ID condition as part of the WHERE clause, otherwise, leave it blank, and it will give you ALL people who are not registered for ALL possible classes via a Cartesian product (since no direct join between user and courses table) .. this could be VERY large / time consuming if trying to run for everyone and your course table is 100s of courses where someone would only be involved in 2-3-4 courses.

select 
      u.id,
      c.id CourseID
   from 
      users u,
      courses c
   where
          u.id = 1
      AND c.id NOT IN 
           ( select ec.Course_ID
                from enrolled_courses ec
                where ec.user_id = u.id )

Answer 3

SELECT
    *
FROM
    courses c
WHERE
    c.id NOT IN (
    SELECT
        ec.course_id
    FROM
        enrolled_courses ec
    WHERE
        ec.user_id = 1 AND ec.course_id IS NOT NULL
    )