Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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
    Harman Tuteja Joins Sikh Philosophy Network! New SPN'ers Dec 25, 2016
    Gurnam Kaur Khalsa Joins Sikh Philosophy Network! New SPN'ers Dec 22, 2016
    Barkha Joins Sikh Philosophy Network! New SPN'ers Dec 22, 2016
    Avaa77 Joins Sikh Philosophy Network! New SPN'ers Dec 20, 2016
    Learningsingh295 Joins Sikh Philosophy Network! New SPN'ers Dec 20, 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.

    --
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page