Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: SQL, nulls in joins

    i have a query along the lines of the SQL below (table & field names changed to protect the innocent - hope i didn't make a typo).
    my query works 100% when the join fields have values
    my table allows nulls
    my query doesn't find records where one of the join fields is null

    i would still like to allow nulls if possible, but i need to see the records with null join fields ...is there a cunning way to do this?

    izy



    SELECT
    tblAg.IDag, tblAg.strPa, tblAg.dtFo, tblCu.strCu,
    tblCo.strCo, tblSf.strSf, tblBu.strBc, tblSr.strSr

    FROM tblCu
    INNER JOIN (tblSr
    INNER JOIN (tblCo
    INNER JOIN (tblBu
    INNER JOIN (tblSf
    INNER JOIN tblAg
    ON tblSf.IDSf = tblAg.IDSf)
    ON tblBu.IDBu = tblAg.IDBu)
    ON tblCo.IDCo = tblAg.IDCo)
    ON tblSr.IDSr = tblAg.IDSr)
    ON tblCu.IDCu = tblAg.IDCu

    WHERE (((1=1)) AND ((tblAg.IDCap) =4));
    currently using SS 2008R2

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select *
    from tableA ta
    INNER JOIN
    tableB tb ON
    ta.id = tb.id OR (ta.id IS NULL AND tb.id IS NULL);
    Last edited by r123456; 07-05-04 at 22:46.
    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
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I think you are talking about an outer join....

    Try changing the inner to Left or Right.

    Greetz

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
    SQL> select NVL(a.name, -1) from a;
    
    NVL(A.NAME,-1)
    --------------
                 1
                 2
                -1
    
    SQL> select NVL(b.name, -1) from b;
    
    NVL(B.NAME,-1)
    --------------
                 1
                 2
    
    SQL> select NVL(a.name, -1), NVL(b.name, -1)
      2  from a
      3  LEFT OUTER JOIN
      4  b ON
      5  a.name = b.name;
    
    NVL(A.NAME,-1) NVL(B.NAME,-1)
    -------------- --------------
                 1              1
                 2              2
                -1             -1
    
    SQL> select NVL(a.name, -1), NVL(b.name, -1)
      2  from a
      3  INNER JOIN
      4  b ON
      5  a.name = b.name OR (a.name IS NULL AND b.name IS NULL);
    
    NVL(A.NAME,-1) NVL(B.NAME,-1)
    -------------- --------------
                 1              1
                 2              2
    
    SQL> insert into b values (NULL);
    
    1 row created.
    
    SQL> select NVL(a.name, -1), NVL(b.name, -1)
      2  from a
      3  INNER JOIN
      4  b ON
      5  a.name = b.name OR (a.name IS NULL AND b.name IS NULL);
    
    NVL(A.NAME,-1) NVL(B.NAME,-1)
    -------------- --------------
                 1              1
                 2              2
                -1             -1
    Last edited by r123456; 07-06-04 at 03:08.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    robert, nice test script, excellent job
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    p.s. now add a null to a and a few more nulls to b and see the results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You get a nice many to many... Which is why i was thinking outer join......


  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    OUTER JOIN is indeed what you're looking for. If you're working in your adp, you can use "FULL OUTER JOIN" instead of "INNER JOIN". I'm not sure how to allow nulls from BOTH sides using jetsql...
    oh yeah... documentation... I have heard of that.

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

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Teddy,

    this is SQL-Server passthru - don't care about jetsql.

    if i've understood what all you wise gurus are saying - i'm stuffed: null foreign keys don't play.

    meanwhile, i changed the data in the table: record 1 in the lookups is "Unknown" or something appropriate like that and the table defaults it's foreign keys to 1 and the relevant combos _BeforeUpdate() protests when it is null.

    fine - it works today!

    tomorrow, some idiot superuser is going to kill record 1 and i'm stuffed again! i hate the idea of having "magic" records in my table!!!!!!!

    how do i get from here to reality?

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    With "full outer join" my friend. It will return the recordset you would expect from an inner join, then pull all the non-matching records, substituting null for the non-matching items.

    MSSQL server supports it exactly as quoted.
    oh yeah... documentation... I have heard of that.

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

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    soooooper: trying now.

    izy
    currently using SS 2008R2

Posting Permissions

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