SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with a couple of queries

    Hi All,

    Wondering in anyone can assist me with a couple of queries which have cropped up in a project. The project is a database of people and their attendance of training courses.

    There are a few tables...

    Users (with UserID and Username)

    Teachers (with TeacherID and TeacherName)

    Courses (with CourseID, CourseName, CourseDate and TeacherID) - this one relates to which teacher took which course

    Attendances (with UserID and CourseID) - this one relates which user attended which course

    What the client is after is firstly selecting all the users who have NOT attended a specific teachers' courses. In other words, if the have been on a course with the specified teacher, they will not be shown.

    Secondly, the client wants the same query as the first but also limiting it to the last 6 months. In other words, selecting all the users who have NOT attended a specific teachers' courses within the last six months.

    This one has got me a bit baffled so any assistance would be appreciated.

    Rik

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,400
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    LEFT JOIN

  3. #3
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that but any other tips? I'm afraid I'm a bit out of my depth with this one and the client is really coming down on me! Any assistance appreciated.

    Rik

  4. #4
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the suggestions. Obviously I am now using the proper database and column names. I've currently got...

    Code:
    SELECT DISTINCT * FROM users INNER JOIN attendances ON database.id = attendances.RespondentID LEFT JOIN events ON attendances.EventID = events.EventID AND events.ModID = 57 WHERE events.EventID IS NULL GROUP BY users.id ORDER BY `date_updated` desc LIMIT 0,50
    Which seems to be working apart from one thing. If a user has attended an event by the particular moderator and an another event by another moderator, it still appears on the list of result. I.e. if they attended an event by ModID=57, they should be removed from the list regardless of if they have attended an event with another ModID.

    This is getting curiouser and curiouser!

    Rik

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by RikStryker View Post
    Obviously I am now using the proper database and column names.
    obviously not quite
    Code:
    SELECT DISTINCT * 
      FROM users 
    INNER 
      JOIN attendances 
        ON database.id = attendances.RespondentID 
    LEFT 
      JOIN events 
        ON attendances.EventID = events.EventID
       AND events.ModID = 57 
     WHERE events.EventID IS NULL 
    GROUP 
        BY users.id 
    ORDER 
        BY date_updated DESC LIMIT 0,50
    also, you will not need to use that DISTINCT if you have GROUP BY users.id
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much for that. Just got to figure out the how to eliminate the rows where the user has attended another course held by a different ModID - hmmm. I guess I could do it through PHP but I really wanted one query to do all the work.

    Rik

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,400
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Can you post the query you have now, and a data example of the problem you're encountering?

  8. #8
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Can you post the query you have now, and a data example of the problem you're encountering?
    Hi Guido,

    Thanks for taking a look.

    Code:
    users
    -------------------------
    id | UserName
    
    attendances
    -------------------------
    AttendanceID | RespondentID | EventID
    
    events
    -------------------------
    EventID | EventName | EventDate | ModID
    
    moderators
    -------------------------
    ModID | ModName
    And here is the query I'm having trouble with as it stands...

    Code:
    SELECT * FROM users INNER JOIN
    attendances ON users.id = attendances.RespondentID
    LEFT JOIN events ON attendances.EventID = events.EventID AND events.ModID = [ModID Value Goes Here]
    WHERE events.EventID IS NULL
    GROUP BY users.id
    ORDER BY `date_updated` desc
    ...in addition, the client has now asked for the above query to include users who have never attended a course - it just gets better?

    Also, the above query has another problem. If the user attended an event with a certain moderator, that record is now shown. However, if they happen to have attended another event with a different moderator, so I end up with duplicates and rows which should not appear. I.E. if a user attended 1 event which was held by the moderator I'm trying to exclude, that's fine. However, If they have attended 3 events, one of which was held by a moderator who I'm trying to exclude, the other two records show up and that is not what I'm after. Therefore, if the user attended an event by the moderator I wish to exclude results from, I don't want them to appear even if they have attended other events with other moderators.

    Sorry for the long-winded reply but I really appreciate any hints, tips and assistance for a guy whose more at home with Photoshop than the Command Line!

    Again, thanks for taking a look,

    Rik

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,400
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT * 
    FROM users 
    LEFT JOIN  (
      SELECT DISTINCT
           users.id
      FROM users
      INNER JOIN attendances 
      ON users.id = attendances.RespondentID
      INNER JOIN events 
      ON attendances.EventID = events.EventID 
      AND events.ModID = [ModID Value Goes Here]
    ) as ue
    ON users.id = ue.id
    LEFT JOIN attendances 
    ON users.id = attendances.RespondentID
    LEFT JOIN events 
    ON attendances.EventID = events.EventID 
    WHERE ue.id IS NULL
    GROUP BY users.id
    ORDER BY `date_updated` desc
    This query should give you all users, excluded those that did an event with the specified moderatore (the left join with the subquery takes care of that) and included those that never did any event (changing the INNER JOIN with the attendances table to a LEFT JOIN takes care of that).

    I didn't test it though...

  10. #10
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, thanks very much. Whilst locked away in a dark room I thought it over and perhaps I am over complicating this a little. The following seems to be doing the job but testing to be done...

    Code:
    SELECT DISTINCT * 
    FROM users
    WHERE users.id NOT IN (
    SELECT RespondentID
    FROM attendances, events
    WHERE attendances.EventID = events.EventID
    AND events.ModID =[VALUE GOES HERE]
    )


Tags for this Thread

Bookmarks

Posting Permissions

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