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

    Unanswered: query data that doesn't match

    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.

  2. #2
    Join Date
    Mar 2011
    Posts
    2
    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

Posting Permissions

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