Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Left Outer Join w/ condition.

    Hi,
    Hi,

    I am trying to do a left outer join. I have two tables one has questions for forms and the other has the answers submitted by the users.
    When i query the Database i would like the join to bring back the recordset with all the questions for a particular form and all the answers
    for that form that a particular user has submitted. Every question will not be answered by the user. I still would like the recordset
    to contain all questions possible for the form..

    I can do a query like the follow:

    SELECT FQ.QuestionType, FQ.Title, FQ.FormQuestionID, FA.AnswerText
    FROM FormQuestions AS FQ
    LEft Outer
    JOIN FormAnswers AS FA ON FA.FormQuestionID=FQ.FormQuestionID;

    This will bring back all the Questions, even if they haven't been answered, plus the answers, but this will bring back all the questions and answers for all users.
    I would like to just bring back all questions and answers for a specific user, something like the following:

    SELECT FQ.QuestionType, FQ.Title, FQ.FormQuestionID, FA.AnswerText
    FROM FormQuestions AS FQ
    LEFT OUTER
    JOIN FormAnswers AS FA ON FA.FormQuestionID=FQ.FormQuestionID
    WHERE And FA.UserID='2004012523121556'
    ORDER BY FQ.SortOrder;

    But this will only bring back the recordset with just the questions and answers that have been answered not all questions.
    This is the query in SQL SERVER:


    SQL = "SELECT FQ.[QuestionType], FQ.[Title], FQ.[FormQuestionID], FA.[AnswerText] " & _
    "FROM FormAnswers FA, FormQuestions FQ " & _
    "WHERE FA.[FormQuestionID] *= FQ.[FormQuestionID] " & _
    "ANDFQ.[FormPageSectionID] = " & FormPageSectionID & " " & _
    "AND FA.[UserID] = '" & UserID & "' " & _
    "ORDER BY FQ.[SortOrder] ASC"



    It works fine. I just can't seem to port it over to Access. Any suggestions?

    -Warren

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you want looks like this:
    Code:
    select fq.sortorder
         , fq.questiontype
         , fq.title
         , fq.formquestionid
         , fa.answertext
      from formquestions as fq 
    left outer 
      join formanswers as fa 
        on fq.formquestionid 
         = fa.formquestionid
       and fa.userid = '2004012523121556'
     where fq.formpagesectionid = 937
    order 
        by fq.sortorder
    access 97 doesn't support that type of join, where there's a condition on one of the tables in the ON clause -- later versions may, i dunno, haven't tested them

    however, there is a workaround:
    Code:
    select fq.sortorder
         , fq.questiontype
         , fq.title
         , fq.formquestionid
         , fa.answertext
      from formquestions as fq 
    inner
      join formanswers as fa 
        on fq.formquestionid 
         = fa.formquestionid
     where fa.userid = '2004012523121556'
       and fq.formpagesectionid = 937
    union all
    select fq.sortorder
         , fq.questiontype
         , fq.title
         , fq.formquestionid
         , null
      from formquestions as fq 
     where fq.formpagesectionid = 937
       and not exists
           ( select 1 
               from formanswers
              where formquestionid 
                  = fq.formquestionid 
                and userid = '2004012523121556' )
    order 
        by fq.sortorder
    NOT EXISTS works in access 97 and will work in other versions too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    2
    Thank you so much. Just ot let you know the fisrt statement is not supported in Access 2000. I tried that statement earlier and it just gives an error. However, the second one works like a champ. Thanks again.

Posting Permissions

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