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 6 of 6
  1. #1
    New Coder
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Select JOIN help

    Im trying to query my database to see which users arent assigned to a task and display only those.

    my tables are set up like so:

    assignments:


    users:


    so in my assignments, I can get it to show me everyone whos know assigned to ANY tasks, but not just specifically to that task..

    Code:
    $userquery = "SELECT * FROM users LEFT JOIN assignments ON users.id = assignments.userid Where assignments.todoid != $listid and assignments.userid is NULL order by full_name asc";
    is what Im using (this returned nothing, but if I removed the assignments.todoid != $listid part then I get everybody not assigned to anything)

    Please help me.. im sure its somethings stupid and easy that i just cant see.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I don't follow. Your requirement:

    Im trying to query my database to see which users arent assigned to a task and display only those.
    Seems to have been accomplished according to what you say here:

    if I removed the assignments.todoid != $listid part then I get everybody not assigned to anything

  • #3
    New Coder
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry, that does sound confusing....
    if there are a,b,c,d as users I need to return the ones taht arent assigned to that specific task
    task 1 (needs to return d)
    a
    b
    c

    task 2 (needs to return c and d)
    a
    b

    task 3 (needs to return b and d)
    a
    c


    what I have would only return D for all of them because its not assigned to ANY of the tasks...

    Thanks!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,576
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    So what you are calling "task 1" in this new post is the same as "todoid" in your first post, yes?

    Let's re-create your tables but showing data that matches your newer post description. Simplifying a little for sake of brevity:
    Code:
    Table: assignments
    todoid :: userid 
       1   ::    a
       1   ::    b
       1   ::    c
       2   ::    a
       2   ::    b
       3   ::    a
       3   ::    c
    
    Table: users
    userid :: name
       a   :: abigail
       b   :: brian
       c   :: cora
       d   :: doug
    Okay so far? (Used letters for userid in both tables for clarity only.)

    So there's probably a simpler way to do this, but this seems to work:
    Code:
    CREATE VIEW fullOuter
    AS
    SELECT CAST(A.todoid AS VARCHAR) + ':' + CAST(U.userid AS VARCHAR) AS fullid, A.todoid, U.userid
    FROM assignments AS A, users AS U
    WHERE 1 = 1;
    // (the WHERE clause there may not be needed, but won't hurt)
    
    CREATE VIEW matchesOnly
    AS
    SELECT CAST(A.todoid AS VARCHAR) + ':' + CAST(U.userid AS VARCHAR) AS fullid
    FROM assignments AS A, users AS U
    WHERE A.userid = U.userid;
    
    CREATE VIEW notAssigned
    AS
    SELECT fullid, todoid
    FROM fullOuter
    WHERE fullOuter.fullid NOT IN ( 
            SELECT fullid FROM matchesOnly 
        );
    
    SELECT NA.todoid, NA.userid, U.name
    FROM 
    notAssigned AS NA, users AS U
    WHERE NA.userid = U.userid
    ORDER BY NA.todoid, U.name;
    Naturally you can pull some (all?) of those VIEWs into the main query. I just find it easier to think of the problem if I created the VIEWs first.

    Basically, that first VIEW produces a full outer join (a.k.a. "cartesian product") of all tasks with all users.

    Then the second VIEW produces a list of all users *AND* their assigned tasks.

    You can hopefully see that if you subtract the second view from the first one, you get all the task/user combinations that are *NOT* assigned. And that's all the third view does.

    So then you can join that third view back to the original table(s) to get all the data you need.

    Clearly, you could have the full outer join (first view, "fullOuter") return *all* the fields you need, so that then the third view ("notAssigned") would be the end product. Whether you want to do that or not depends on how much data you will be working with and/or how efficiently MySQL can handle this. My guess is that you could indeed do that, thus saving a step. And, more than likely, you can actually combine steps one and three. (You can probably even combine all three steps, but I just find *especially* step two to be attractive as something outside the main query.)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,576
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    By the by, I *did* test this (albeit with Access...sorry!) and it does produce the right results.

  • #6
    New Coder
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    1
    Thanked 0 Times in 0 Posts
    holy crap.. i figured it was just something simple that I was forgetting about... I'll try this out and see if it works.. thanks!


  •  

    Posting Permissions

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