Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: Left join won't work for me

    I will simplify my real tables (Q represents Question in the field names):

    Table: Q1
    -----------
    Study
    QType
    QNum

    Table: Q2
    -----------
    Study
    PersonID
    QType
    QNum
    Answer1

    Table Q1 has 3 records for QType "C". Table Q2 has a PersonID that only answered 2 of the 3 questions in Q1 for type "C". I am trying to get ALL of the QType and QNum from table Q1 and matching records/nulls by joining on table Q2. I can't seem to get the nulls. Assume in table Q2 PersonID 123 answered QNums 1 and 2, but there are QNums 1,2, and 3 in table Q1. I want output like this:

    Code:
    Study     PersonID     QType     QNum     Answer1
    ---------------------------------------------------------
    0001      123             C             1           555
    0001      123             C             2           777
    0001      123             C             3           (null)
    But I can only get a recordset with the records for QNums 1 and 2, not the null for QNum 3.

    Here is the SQL:

    Code:
    SELECT Q1.QNum, 
                Q1.QType, 
                Q2.PersonID, 
                Q2.QNum, 
                Q2.QType, Q2.Answer
                FROM Q1 LEFT JOIN Q2 ON (Q1.QType = Q2.QType) 
                              AND (Q1.QNum = Q2.QNum) 
                              AND (Q1.Study = Q2.Study)
    WHERE (((Q1.QType)="C"))
    ORDER BY Q2.PersonID;
    I know that I am selecting both Q1 and Q2's QNum and QType in the query, it is just for testing.

    Thanks for any help!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Switch your join around ... Q2 has to be the controlling table for this to work ...

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    Thanks for your reply. The problem is, Q2 does not have all 3 of QType "C" for one of the PersonID. The rest of the PersonIDs have all 3 QType "C".

    So if I were to query a recordset, one PersonID will be missing a record for the unanswered question. I really need that PersonID to have a record of the 3rd QType with a NULL answer.

    Otherwise, I will have to do a LOT more programming as I loop through the recordset to handle 3 QTypes per PersonID. I would have to count the number of records processed per PersonID. Of course it would work, but seems it would be against programming and data access principles. :-/

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You misunderstand me ... I mean literally just change the Q1 for the Q2 and vice versa ...

Posting Permissions

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