Help with a couple of queries

[RIkStryker]

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

[vinny42]

This sounds very much like a school assignment, am I right? :-)

[Barand]

Looks like you need a date column in the attendance records, or are you just using course dates for the final requirement?

[RIkStryker]

This sounds very much like a school assignment, am I right? :-)

 

Actually it's a favour for a friends' new business. Everything is done apart from this darn query which has got me banging my head against the wall!

[RIkStryker]

Looks like you need a date column in the attendance records, or are you just using course dates for the final requirement?

 

I was planning on using just the course date.

[vinny42]

 

Actually it's a favour for a friends' new business. Everything is done apart from this darn query which has got me banging my head against the wall!

 

You mean to say that you have built an application for registering course attendance and you never once used a join in the entire application? :-)

 

 

 

I was planning on using just the course date.

 

You can, but then you'd have to make the combination of name+date unique, because that will be your primary key.

 

Personally I'd run thought the rules of normalization and put the database in 3d normal form.

[RIkStryker]

 

 

You mean to say that you have built an application for registering course attendance and you never once used a join in the entire application? :-)

 

Its a WordPress site which uses two plugins - Your Members and Participants Database. I was brought in hook them together and add extra admin search criteria - which was all good and is done. Then the client asks for this functionality and I hit my MySQL limit!

[Barand]

No data so untested, try

 

1.

SELECT username
FROM users u
INNER JOIN attendances a USING (userID)
LEFT JOIN courses c
    ON a.courseID = c.courseID
    AND TeacherID = ?
WHERE c.courseID IS NULL

2.

SELECT username
FROM users u
INNER JOIN attendances a USING (userID)
LEFT JOIN courses c
    ON a.courseID = c.courseID
    AND TeacherID = ?
    AND courseDate > CURDATE() - INTERVAL 6 MONTH
WHERE c.courseID IS NULL

Edited September 9, 2013 by Barand

[RIkStryker]

Thanks very much, Ill give it a shot.

 

Rik

[RIkStryker]

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