Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Unanswered: still working on that JOIN statement...

    I'll try and explain a little more in-depth...

    The application I'm working on is for keeping track of student training. The way the database is set up for tracking is this:

    "Tasks" table: There certain tasks which students must be able to perform or demonstrate knowledge about. Depending on the experience level the student is studying to achieve, they might have a different list of tasks, but ALL of the tasks are contained in this table.

    "TaskListItems" table: This table defines the list of tasks that a student is required to perform. It only has two fields - TaskID, which is the ID of the record from the Tasks table it refers to, and TaskListID, which refers to the ID of the Task List.

    "TasksCompleted" table: When a student starts or completes a task, more information is added to the database specific to that student and the task. This table has the fields "TaskID", which is the ID of the record from the Tasks table it refers to, UserID, which is the ID of the User the information pertains to, as well as other non-relational information.

    I have figured out how to list all of the tasks on a user's task list by using this SQL statement:

    strSQL = "((SELECT Tasks_1.*" _
    & " FROM TaskListItems AS TaskListItems_1" _
    & " INNER JOIN Tasks AS Tasks_1" _
    & " ON TaskListItems_1.TaskID = Tasks_1.ID" _
    & " WHERE (TaskListItems_1.TaskListID=" & TaskListID _
    & " AND TaskListItems_1.Removed=False" _
    & " AND Tasks_1.AFSCID=" & CurUser.AFSC.ID _
    & " AND Tasks_1.ParentID=" & ID & ") ORDER BY Tasks_1.Index)" _
    & " UNION" _
    & " (SELECT Tasks_2.* FROM Tasks AS Tasks_2" _
    & " WHERE Tasks_2.Heading=True" _
    & " AND Tasks_2.AFSCID=" & CurUser.AFSC.ID _
    & " AND Tasks_2.ParentID=" & ID & " ORDER BY Tasks_2.Index))"

    I am trying to figure out how to join the "TasksCompleted" table as well. I have tried using a join in the first SELECT statement and using the WHERE statement to quantify which records are selected from the TasksCompleted table, but that only selects records where either 1) There is a matching record for that task in TasksCompleted for that user, or 2) There are no matching records for that task at all. It leaves out records where the User in question has does not have a record for a task in TasksCompleted, but other users do. This causes the Task itself to not be displayed at all.

    I'd like to try and take the statement above and add a LEFT JOIN statement to the TasksCompleted table for the entire statement, which would effectively be this:

    SELECT Tasks.*, TasksCompleted.* FROM (sql statement from above) LEFT JOIN TasksCompleted

    but I can't figure out the correct syntax. I've been working on this for the last 2 days and I can't get it right. Any help would be *greatly* appreciated. Thank you much.

    Dan

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's see if i have this right

    TaskListItems relates Tasks to TaskLists, but TaskLists is not shown

    TaskCompleted relates Tasks to Users, and therefore the same user cannot complete the same task in a different TaskList?

    the relationships appear wonky

    how are users related to task lists?

    please give a few sample rows of all 4 tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    9
    The forums screws up the spacing, so included a text file... Take a look, I'll explain if there's still that's foggy.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, thanks for the data, now i have even more problems understanding

    you said "I have figured out how to list all of the tasks on a user's task list "

    however, there is nothing in the data that shows which task list belongs to a user

    the only table related to user is taskcompleted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    9
    Oy... sorry...

    There is another table, "TaskListAssignments"

    Fields are UserID, TaskListID.

    Here's how it goes...

    Tasks are entered.
    Task Lists are created using the Tasks.
    A User is assigned a Task List.
    When a User starts a task, a record is created in TasksCompleted, with references to the task and user. That record is updated when the task is completed.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aha

    okay, go back a few posts, to that ugly query you wrote with the UNIONs which purported to be the list of tasks that a user has, it would be replaced with this:
    Code:
    select TL.Name   as TaskListName 
         , T.Name    as TaskName     
      from Users U
    inner
      join TaskListAssignments TLA
        on U.UserID = TLA.UserID     
    inner
      join Tasks T
        on TLA.TaskID = T.TaskID
     where U.Name = 'Fred'
    so then to see how Fred has done, you just join the TasksCompleted table, except now you actually have the right keys to join it with
    Code:
    select TL.Name   as TaskListName 
         , T.Name    as TaskName     
      from Users U
    inner
      join TaskListAssignments TLA
        on U.UserID = TLA.UserID     
    inner
      join Tasks T
        on TLA.TaskID = T.TaskID
    left outer
      join TasksCompleted TC 
        on TLA.UserID = TC.UserID
       and TLA.TaskID = TC.TaskID     
     where U.Name = 'Fred'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Posts
    9

    sweet.

    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
  •