Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011

    Unanswered: query data that doesn't match

    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.

    id	firstName       lastName        email
    1       John            Smith 
    2       Jane            Doe   
    3       Bob             Ross  
    4       Steph           Smith 
    id      name            url
    1       whatever
    2       something
    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.

  2. #2
    Join Date
    Mar 2011
    ok after a bit of research and help from others, I figured it out:

    SELECT DISTINCT, e.firstName, e.lastName,
    FROM (employees e, videos v)
    LEFT OUTER JOIN viewhistory h ON h.videoId = AND h.employeeId =
    The above SELECT statement will return all employees that have at least ONE unwatched video

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts