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

LEFT JOIN

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

Thanks for the suggestions. Obviously I am now using the proper database and column names. I’ve currently got…

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

obviously not quite

SELECT DISTINCT * 
  FROM users 
INNER 
  JOIN attendances 
    ON [COLOR=Red]database[/COLOR].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

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

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.

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…

SELECT * FROM users INNER JOIN
attendances ON users.id = attendances.RespondentID
LEFT JOIN events ON attendances.EventID = events.EventID AND events.ModID = [I][ModID Value Goes Here][/I]
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


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… :wink:

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…

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]
)