Need help with query

[Ric]

Hello everyone!

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:[1][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!

Thanks,

Ric

[Old Pedant]

The problem: Unless you want to return the SAME NUMBER of courses for each user, you can't do this in SQL.

That is you could *NOT* get back:
[1][jdoe][John][Doe][01-29-2012]
[2][adam][Adam][And][12-31-2012][2-29-2012][3-31-2012]
because you can not have a different number of columns per row.

You *could* do this with a combination of SQL and PHP/JSP/ASP code.

Or you would get back a *list* of dates (say comma delimited) as a single column:
[1][jdoe][John][Doe][01-29-2012]
[2][adam][Adam][And][12-31-2012,2-29-2012,3-31-2012]

Though I don't understand how you will interpret that if you don't know what date goes with what course.

Now, in your example, you showed exactly *two* courses, 1 with "no result". If you want exactly two courses for each person, *that* we can do, although it's not trivial at all.