[RESOLVED] Left join problems

[deshg]

Hey everyone,

I am just trying to write a mysql query to pull some info out of my database and i'm slightly stuck, i would really appreciate any pointers as i think i'm being thick! I have a Tests table (containing data about individual tests), a Questions table (containing multiple choice questions each associated to a specific test) and an Answers table (containg answers each associated to a specific question). I also have a UserAnswers table which lists the answers that were given to each question for each specific user.

I am just trying to write a query to pull a list of questions (for a specific test) that have not been answered by a specific user. I can do a left join query like so:

SELECT * FROM Questions LEFT JOIN UserAnswers ON Questions.Fid = UserAnswers.FQuestionID WHERE UserAnswers.FQuestionID IS NULL AND Questions.TestID='$tid'

This returns a list of any questions within test '$tid' that don't have any related answers within UserAnswers (from any user). I need to make this more specific so it shows them for a specific user, however i cannot for the life of me work out how to do that? The UserAnswers table obviously has a userid field which details which user gave the answer but i can't work out how to integrate this into the query.

Any help anyone could give, or any pointers as to what sort of construct i should be looking at would be massively appreicated. Obviously i could do this in php after pullin extra info in from the db but i'd rather not do that if possible.

Thanks for your help as ever everyone!

Dave

[bradgrafelman]

deshg wrote:

I need to make this more specific so it shows them for a specific user

Sounds like you could simply change this:

LEFT JOIN UserAnswers ON Questions.Fid = UserAnswers.FQuestionID

to this:

LEFT JOIN UserAnswers ON (Questions.Fid = UserAnswers.FQuestionID AND UserAnswers.userID = 5)

where '5' is obviously a variable value that you'd insert into the query.

EDIT: Hrm.. maybe I'm over thinking this. Can't you just add the "userID = 5" bit to the WHERE condition?

[deshg]

I thought that myself but when i add userID=5 to the WHERE i get no results at all. But as suspected i was indeed being thick and your post showed why, i had actually already done nearly what you suggested above but i missed out the brackets for the ON clause, i think that was where the problem was!

Thanks so much for your help,

Dave

[bradgrafelman]

So did that resolve your issue? If so, don't forget to mark this thread resolved using the link on the Thread Tools menu.

[deshg]

Sorry brad, forgot to do that, will do it now! Thanks again!