I need help comming up with a query.
I have 3 tables: users, courses and results. The results table has 2 foreign keys (user_id and course_id).
The result I want to get is as follows:
All the data of the user (id, username, first_name, last_name) and then for each course I want the latest date of the result table if there is one and only when the status is either 'passed' or 'attended'. If there is no result, or the status is not 'passed' or 'attended' the result should say 'no result'.
Preferably it will look something like this:
Columns:[user id][username][first name][last name][course 1][course 2]
Example row:[jdoe][John][Doe][01-29-2012][no result]
I've been at it for many hours now, and I can't figure it out. If anyone has any ideas, I'd really appreciate it!