query specific data help

[atomicrabbit]

Hey guys,

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.

 

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 John, Bob & Steph because Jane is the only employee that watched BOTH videos.

[atomicrabbit]

I've tried this based on a suggestion by someone, but I get an error: #1054 Unknown column 'employees.id' in 'on clause'

SELECT employees.id 
FROM employees, videos
LEFT OUTER JOIN viewhistory ON viewhistory.employeeId = employees.id AND viewhistory.videoId = videos.id
WHERE viewhistory.id IS NULL

 

No idea why!

[atomicrabbit]

After a bit of research, I found this:

 

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

 

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

 

...

 

SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

 

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

 

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

 

Alternatively, avoid the use of the comma operator and use JOIN instead:

 

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

 

This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.

Source: http://dev.mysql.com/doc/refman/5.0/en/join.html

 

So after modifying my query and adding the brackets around the "employees, videos", I got this:

SELECT employees.id 
FROM (employees, videos)
LEFT OUTER JOIN viewhistory ON viewhistory.employeeId = employees.id AND viewhistory.videoId = videos.id
WHERE viewhistory.id IS NULL

 

... which worked like a charm! It returns all employees that have at least ONE unwatched video.