Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

mysql and joins

  • 11-03-2004 04:22PM
    #1
    Subscribers Posts: 1,911 ✭✭✭


    I have three tables:
    users (user_id, username)
    classlists (user_id, module_id)
    submissions (user_id, module_id, assignment_id, submission_id)

    I am trying to get an end result where I have a table of all the users in a module and their submission for an assignment. If there is no submission, I want nulls instead.
    ie
    user_id | module_id | assignment_id | submission_id
    1 | 1 | 1 | 1
    2 | 1 | 1 | 2
    3 | 1 | null | null

    I'm using the following query:
    SELECT * FROM 
    (classlists INNER JOIN users ON (classlists.user_id = users.user_id))
     LEFT JOIN 
    submissions ON (users.user_id = submissions.user_id) 
    WHERE
     (assignment_id = 1 OR assignment_id IS NULL) AND classlists.module_id = 1
    

    Instead of getting what I want above, I am just getting rows with all the info in them.

    am I going the right direction? or just barking up the wrong tree?


Comments

  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    try this

    Select sub.* From
    classLists cs Left outer join submissions sub on (cs.userid =sub.userid)
    or am I missing something ?

    the other thing is in classlists you should really have a ClassListsID and use that in the submissions table (normalisation and all that jazz)


Advertisement