Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Ric
    Ric is offline
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with query

    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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •