Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

join two tables with Blank fields

Discussion in 'Information Technology' started by jj, Jul 28, 2006.

  1. jj

    jj
    Expand Collapse
    Guest

    Hi Ng

    I got two tables with adresses - With street name, postal number, house
    number, Level (If there is more levels), Letter (if there is letters).

    Now I would like to join these two table (so I make a join between postal
    number, street, housenumber, level, Letter - But I only finds the records
    with a value in each field!! - If there is a blank in for example Level -
    then this record will not show up.

    Can anyone tell me how I can get these records too?

    TIA
    JJ
     
  2. Loading...

    Similar Threads Forum Date
    Khalis International Joins Sikh Philosophy Network! New SPN'ers Nov 23, 2016
    Gurdeep Kaur Joins Sikh Philosophy Network! New SPN'ers Nov 22, 2016
    Arshdeepsingh Joins Sikh Philosophy Network! New SPN'ers Nov 22, 2016
    Diljit Singh Joins Sikh Philosophy Network! New SPN'ers Nov 19, 2016
    Cynthia Agard Joins Sikh Philosophy Network! New SPN'ers Nov 19, 2016

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Sounds as though they're Null (as opposed to blank).

    Go into the SQL associated with your query.

    Where you've currently got something like

    ON Table1.Field1 = Table2.Field1

    , you need to change it to


    ON (Table1.Field1 = Table2.Field1) OR (Table1.Field1 IS NULL AND
    Table2.Field1 IS NULL)

    Alternatively, you could use:

    ON Nz(Table1.Field1, X) = Nz(Table2.Field1, X)

    where X is some value that you know does not appear as a value for Field1.
    If Field1 is a text field, make sure you've got quotes around it:

    ON Nz(Table1.Field1, "X") = Nz(Table2.Field1, "X")

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "jj" <jj@get2net.dk> wrote in message
    news:OOfzy9VjGHA.3408@TK2MSFTNGP05.phx.gbl...
    >
    > Hi Ng
    >
    > I got two tables with adresses - With street name, postal number, house
    > number, Level (If there is more levels), Letter (if there is letters).
    >
    > Now I would like to join these two table (so I make a join between postal
    > number, street, housenumber, level, Letter - But I only finds the records
    > with a value in each field!! - If there is a blank in for example Level -
    > then this record will not show up.
    >
    > Can anyone tell me how I can get these records too?
    >
    > TIA
    > JJ
    >
     
  4. jj

    jj
    Expand Collapse
    Guest

    Thx a lot Douglas
    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> skrev i en
    meddelelse news:%23QfLzdWjGHA.4512@TK2MSFTNGP04.phx.gbl...
    > Sounds as though they're Null (as opposed to blank).
    >
    > Go into the SQL associated with your query.
    >
    > Where you've currently got something like
    >
    > ON Table1.Field1 = Table2.Field1
    >
    > , you need to change it to
    >
    >
    > ON (Table1.Field1 = Table2.Field1) OR (Table1.Field1 IS NULL AND
    > Table2.Field1 IS NULL)
    >
    > Alternatively, you could use:
    >
    > ON Nz(Table1.Field1, X) = Nz(Table2.Field1, X)
    >
    > where X is some value that you know does not appear as a value for Field1.
    > If Field1 is a text field, make sure you've got quotes around it:
    >
    > ON Nz(Table1.Field1, "X") = Nz(Table2.Field1, "X")
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "jj" <jj@get2net.dk> wrote in message
    > news:OOfzy9VjGHA.3408@TK2MSFTNGP05.phx.gbl...
    >>
    >> Hi Ng
    >>
    >> I got two tables with adresses - With street name, postal number, house
    >> number, Level (If there is more levels), Letter (if there is letters).
    >>
    >> Now I would like to join these two table (so I make a join between postal
    >> number, street, housenumber, level, Letter - But I only finds the records
    >> with a value in each field!! - If there is a blank in for example Level -
    >> then this record will not show up.
    >>
    >> Can anyone tell me how I can get these records too?
    >>
    >> TIA
    >> JJ
    >>

    >
    >
     
  5. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Douglas J. Steele wrote:
    > > Now I would like to join these two table (so I make a join between postal
    > > number, street, housenumber, level, Letter - But I only finds the records
    > > with a value in each field!! - If there is a blank in for example Level -
    > > then this record will not show up.

    >
    > Sounds as though they're Null (as opposed to blank).


    Note Doug's proposed solution may give unexpected results with an OUTER
    JOIN.

    The OP's situation is an example of bad SQL DDL (schema design) making
    SQL DML (queries) harder to write.

    If a 'blank' address line is supposed to be comparable with a 'blank'
    line in another address then allowing a NULL value is not appropriate.
    The column(s) should be declared as NOT NULL with the (common)
    placeholder value (Doug's 'X' but I prefer the more readable '{{NA}}')
    declared as the default; there should also be a validation rule to
    disallow zero-length strings.

    Getting the database designer to fix the table(s) means the application
    developers will not have to handle the design flaw in *every* query
    they write.

    Jamie.

    --
     

Share This Page