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.
3
Answers
You need a
LEFT
join and filter out the matched rows:The condition
b.UserID = 1
will join the tables only to the courses where that user has enrolled and the conditionWHERE 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.
Try a subquery:
You need to do a
LEFT JOIN
(or a right join). TheLEFT 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,
with no
JOIN
condition, it would pull all the users and all the courses.Now add
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
conditionand 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: