phpmyadmin Question posted in
The official documentation can be found here.

SQL Statement to find only courses on which the current user is not enrolled on – Phpmyadmin

I am developing a web app which keeps track of an employees training records as part of my uni course.

I am using phpMyadmin and MySQLi.

I have 3 database tables tblUser, tblCourse and tblBookings.

tblUser has UserID, First_Name, Last_Name, Email, Access_Level columns

tblCourse has CourseID, Course_Title, Course_Start_Time, Course_Duration, Course_Description, Max_Attendees columns

tblBookings contains only BookingID, CourseID and UserID which links to the relevant user/course in the other two tables.

tblBookings looks something like this
+-----------+----------+--------+
| BookingID | CourseID | UserID |
+-----------+----------+--------+
|         1 |        1 |      1 |
|         2 |        1 |      2 |
|         3 |        1 |      3 |
|         4 |        2 |      2 |
|         5 |        2 |      3 |
|         6 |        3 |      1 |
+-----------+----------+--------+

I am trying to write an SQL statement to populate a table which only displays courses onto which the current user is not enrolled. So in the above scenario for UserID 1 I would want to populate a table with only the course details of the Course with CourseID of 2 as this is the only course which he is not enrolled on.

The closest I have got is with this statement:

SELECT `tblCourseAdmin`.`CourseID`, `Course_Title`, `Course_Date`, `Course_Start_Time`, `Course_Duration`, `Course_Description`, `Max_Attendees`
FROM `tblCourseAdmin`
INNER JOIN `tblBookings`
ON `tblCourseAdmin`.`CourseID` = `tblBookings`.`CourseID`
WHERE`tblBookings`.`UserID` != 1

But this still pulls up courses in which the user is enrolled if there are other users enrolled on that course.

Tags:

3

Answers


  1. 0

    You need a LEFT join and filter out the matched rows:

    SELECT c.*
FROM `tblCourseAdmin` c LEFT JOIN `tblBookings` b
ON c.`CourseID` = b.`CourseID` AND b.`UserID` = 1
WHERE b.`UserID` IS NULL

    The condition b.UserID = 1 will join the tables only to the courses where that user has enrolled and the condition WHERE b.UserID IS NULL will keep only the unmatched rows which are the the courses where that user has not enrolled.
    See a simplified demo.

    Login or Signup to reply.
  2. 0

    Try a subquery:

    SELECT `tblCourseAdmin`.`CourseID`, `Course_Title`, `Course_Date`, `Course_Start_Time`, `Course_Duration`, `Course_Description`, `Max_Attendees`
FROM `tblCourseAdmin`
WHERE CourseID NOT IN (
    SELECT CourseID FROM tblBookings WHERE UserID = 1
)
    Login or Signup to reply.
  3. 0

    You need to do a LEFT JOIN (or a right join). The LEFT JOIN gets one table and matches it to another table no matter what. When there is no match, the columns of the second table get NULLs.

    So if you do,

    SELECT * 
   FROM tblUser
   JOIN tblCourse

    with no JOIN condition, it would pull all the users and all the courses.

    Now add

       LEFT JOIN tblBookings ON (tblUser.UserID = tblBookings.UserID AND tblBookings.CourseID = tblCourse.CourseID)

    and this would pull in all users and all courses, and where there is no booking for that user and that course, the tblBookings columns would be NULL.

    So you now just add a WHERE condition

       WHERE (tblBookings.UserID IS NULL OR tblBookings.CourseID IS NULL)

    and get the rows where there is a user, there is a course, but that user is not enrolled in that course. Which seems to be what you were after?

    UPDATE

    Sorry, it was not what you were after, but close. You want the above, for the current user. So you don’t need to join with the users table after all:

    SELECT * 
   FROM tblCourse
   LEFT JOIN tblBookings ON (tblUser.UserID = CURRENT_USER_ID AND tblBookings.CourseID = tblCourse.CourseID)
   WHERE (tblBookings.CourseID IS NULL)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search