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

    Unanswered: Multiple JOIN statements using ASP

    I need to select data from 2 tables, based on data from 3:

    I am selecting records from the table "Tasks", using LEFT JOIN TasksCompleted ON Tasks.ID=TasksCompleted.TaskID

    I also need to do an INNER JOIN TaskListItems ON TaskListItems.TaskID=Tasks.ID

    and then:

    WHERE TaskListItems.TaskListID=" & TaskListID & " AND Tasks.ParentID=" & ID & " AND Tasks.AFSCID=" & CurUser.AFSC.ID & " AND TasksCompleted.UserID=" & GlobalCurrentUser.ID

    I am attempting this using the sql statement:
    strSQL = "SELECT Tasks.*,TasksCompleted.*,TaskListItems.*" _
    & " FROM Tasks " _
    & " INNER JOIN TaskListItems ON Tasks.ID=TaskListItems.TaskID" _
    & " LEFT JOIN TasksCompleted ON Tasks.ID=TasksCompleted.TaskID" _
    & " WHERE TaskListItems.TaskListID=" & TaskListID & " AND Tasks.ParentID=" & ID & " AND Tasks.AFSCID=" & CurUser.AFSC.ID _
    & " AND TasksCompleted.UserID=" & GlobalCurrentUser.ID

    I am getting this error:
    icrosoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression 'TaskListItems.TaskID=Tasks.ID LEFT JOIN TasksCompleted ON Tasks.ID=TasksCompleted.TaskID'.

    /updatecfetp.asp, line 91




    This is the result of Response.Write strSQL:

    SELECT Tasks.*,TasksCompleted.*,TaskListItems.* FROM Tasks INNER JOIN TaskListItems ON TaskListItems.TaskID=Tasks.ID LEFT JOIN TasksCompleted ON Tasks.ID=TasksCompleted.TaskID WHERE TaskListItems.TaskListID=2 AND Tasks.ParentID=0 AND Tasks.AFSCID=1 AND TasksCompleted.UserID=1


    I'm fairly new to using JOIN, so I'm sure I'm breaking a simple rule or syntax. Any help would be appreciated. Thanks!
    Last edited by DreamThrall; 02-11-04 at 12:08.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select *
    from table1 t1
    INNER JOIN
    table2 t2
    LEFT OUTER JOIN
    table3 t3 ON
    t1.id = t2.id ON
    t2.id = t3.id;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Oct 2003
    Posts
    9
    Alright, now I've got this:

    strSQL = "SELECT *" _
    & " FROM Tasks" _
    & " INNER JOIN TaskListItems" _
    & " LEFT OUTER JOIN TasksCompleted" _
    & " ON Tasks.ID=TaskListItems.TaskID" _
    & " ON Tasks.ID=TasksCompleted.TaskID" _
    & " WHERE TaskListItems.TaskListID=" & TaskListID & " AND Tasks.ParentID=" & ID & " AND Tasks.AFSCID=" & CurUser.AFSC.ID _
    & " AND TasksCompleted.UserID=" & GlobalCurrentUser.ID

    And I'm getting a "Syntax error in FROM clause" error.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    r123456, you forgot the parentheses that Access required

    Code:
    strSQL = "SELECT ...
    & " FROM ( Tasks " _
    & " INNER JOIN TaskListItems ON Tasks.ID=TaskListItems.TaskID" _
    & " ) LEFT JOIN TasksCompleted ON Tasks.ID=TasksCompleted.TaskID" _
    & " WHERE ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    9

    Okay, now with a UNION statement

    Okay, all that is working, but I realized that there are certain records that I need regardless of other information from other tables. These records are in the "Tasks" table and will have the field "Heading" set to "True".

    I tried using this SQL code, but I'm getting a syntax error in the join operation:

    strSQL = "SELECT Tasks.*,TasksCompleted.*" _
    & " FROM (" _
    & "((SELECT Tasks.* FROM Tasks) INNER JOIN TaskListItems ON Tasks.ID = TaskListItems.TaskID)" _
    & " UNION" _
    & " (SELECT Tasks.* FROM Tasks WHERE Tasks.Heading=True))" _
    & " LEFT JOIN TasksCompleted ON Tasks.ID = TasksCompleted.ID " _
    & " WHERE TaskListItems.TaskListID=" & 2 & " AND Tasks.ParentID=" & 0 & " AND Tasks.AFSCID=" & 1 _
    & " AND TasksCompleted.UserID=" & 1

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how are you building that sql? you don't need to nest selects that way, and if you do, i believe you need table aliases

    try this instead:
    Code:
    select Tasks.*,TasksCompleted.*
      from (
           Tasks 
    left outer 
      join TaskListItems 
        on Tasks.ID = TaskListItems.TaskID 
           )
    left outer
      join TasksCompleted 
        on Tasks.ID = TasksCompleted.ID 
     where (
           Tasks.Heading = True
        or TaskListItems.TaskID is not null
           )
       and TaskListItems.TaskListID = 2
       and Tasks.ParentID = 0
       and Tasks.AFSCID = 1
       and TasksCompleted.UserID = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Posts
    9

    Alright, got it working with a slight problem

    I'm using this SQL statement:

    strSQL = "(SELECT Tasks.*" _
    & " FROM TaskListItems" _
    & " INNER JOIN (Tasks " _
    & " LEFT JOIN TasksCompleted ON Tasks.ID = TasksCompleted.ID)" _
    & " ON TaskListItems.TaskID = Tasks.ID" _
    & " WHERE (TaskListItems.TaskListID=" & 2 _
    & " AND (TasksCompleted.UserID=" & 1 _
    & " OR TasksCompleted.UserID Is Null)" _
    & " AND TaskListItems.Removed=False" _
    & " AND Tasks.ParentID=" & ID & "))" _
    & " UNION" _
    & " (SELECT * FROM Tasks AS Tasks_1" _
    & " WHERE Tasks_1.Heading=True" _
    & " AND Tasks_1.AFSCID=" & 1 _
    & " AND Tasks_1.ParentID=" & ID & ")"


    Only only problem comes when there is no record in TasksCompleted that matches the UserID, but there is a record that doesn't match. Therefore, it doesn't return the record from "Tasks" at all...

    How can I get around this?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to "get around" it should not be your mission

    you should try to understand why you are getting the results you are getting, and then combine your tables in the right sequence with the appropriate INNER or OUTER joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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