Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    97

    Unanswered: Null values in Join

    Okay,

    I need to join two tables together where the foreign key has valid values that are null.

    Is there anyway to join on a null value in Access without having to create a separate table and populate that table some value for "null"? (Which is what I usually do but it would be really frustrating in this case.)

    Thanks
    Chris

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try using left or right join.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2002
    Posts
    97

    Left or Right

    Nope, 'cause I have to join to the null value. If I do the a left Join then the "right" table values return with all null values.

    Basically, I need to join where Tbl1_Fld1 = 123 and Tbl2_Fld2 = Null to Tbl2_Fld1 = 123 and Tbl2_Fld2 = Null.

    So usually what I do is I create two scratch tables where I do something like Nz(Fld1, "Null") but it just doesn't work well here.

    -C
    Last edited by Mr_Yabbo; 02-07-06 at 12:15.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Mr_Yabbo
    ...
    where Tbl1_Fld1 = 123 and Tbl2_Fld2 = Null to Tbl2_Fld1 = 123 and Tbl2_Fld2 = Null
    ...
    You said tbl1_fld2 is null twice. Looks to me like you're joining tbl1_fld1 to tbl2_fld1 WHERE tbl2_fld2 IS NULL.... so do that...

    SELECT *
    FROM tbl1 INNER JOIN tbl2 ON tbl1.fld1 = tbl2.fld1
    WHERE tbl2.fld2 IS NULL
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2002
    Posts
    97

    Oops...

    Sorry...should have read.

    Tbl1_Fld1 = 123 and Tbl1_Fld2 = Null to Tbl2_Fld1 = 123 and Tbl2_Fld2 = Null

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So add tbl1_fld2 IS NULL to your where clause. You're overthinking it, I think...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If I understand what you are trying to do, the answer is no. Assuming your data looked like

    Table 1
    --------
    1 Null
    2 Null
    3 Null

    Table 2
    -------
    1 Null
    3 Null

    And you wanted:

    Query 1
    --------
    1 Null
    3 Null

    If this is correct, then you cannot do it because no two nulls are ever equal. So in this case the 1 and the 3 match but the nulls never match and so you won't show any rows in your query.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Which is why you specify the null criteria in the WHERE clause as opposed to the FROM clause....
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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