query data that doesn't match

atomicrabbit · #1 (**permalink**) 03-22-11, 21:23

Hey,
I'm working on a small project. Basically employees need to watch specified videos and when they do, it will be noted in a table. But if they do not, they will get reminders of what videos they haven't watched. I have most of the project working, but what I can't figure out is how to query the database to return all the employees that have any unwatched videos. Check out the following tables.

Code:

employees
id	firstName       lastName        email
---------------------------------------------------
1       John            Smith           a@b.com
2       Jane            Doe             b@c.com
3       Bob             Ross            c@d.com
4       Steph           Smith           d@e.com


videos
id      name            url
---------------------------------------------------
1       whatever        http://domain.com/video1
2       something       http://domain.com/video2


viewhistory
id      employeeId      videoId         watchDate
---------------------------------------------------
1       1               2               2011-03-17
2       2               2               2011-03-17
3       3               1               2011-03-17
4       2               1               2011-03-17

So obviously the employees table holds the employees names, the videos table holds the videos and the viewhistory table keeps entries whenever an employee watches a video. SO if they watch a video, and entry goes in with the employee ID and the video ID they watched.

So what statement would I use to determine all the employees that have ANY unwatched videos

Based on the above tables, the query should return the employees John, Bob & Steph because Jane is the only employee that watched BOTH videos.

atomicrabbit · #2 (**permalink**) 03-23-11, 02:08

ok after a bit of research and help from others, I figured it out:

Code:

SELECT DISTINCT e.id, e.firstName, e.lastName, e.email
FROM (employees e, videos v)
LEFT OUTER JOIN viewhistory h ON h.videoId = v.id AND h.employeeId = e.id
WHERE h.id IS NULL

The above SELECT statement will return all employees that have at least ONE unwatched video

Internet Services

Web Development

Digital Marketing

Consumer Tech