Link to home
Create AccountLog in
Avatar of svs1919

asked on

un matching records in tables

The code below extracts the un matching records from c and b
i have to change code so that c.street1 should compare with b.address2 and rest as it is
2nd thing for some records address ,zip might not match but if state matches the data is not extracted for example
Con Table                        Block table
street1  Street2  state   zip          Address1 Address2   state   zip
xyz            nj     08815              Abc          nj     03310

in this case record is not extracted bcos NJ matches in both tables thats not what i was expecting.if any of these columns will not match i should get the record
      New n
      join Page p On N.ITEM = P.ITEM
      join dbo.Lan l On n.onumber = l.onumber
      Join Con c On c.oId = l.Id
      inner join (
                        count(*) as quantity
                  group by
                 ) q on n.ONUMBER = q.onumber
      Left Outer Join block b (NOLOCK) On N.DEALER_ID = b.CUSTNMBR
      n.ORDER_QUANTITY = q.quantity
and	b.address1 is null and  b.ADDRESS2 is null and is null and b.state is null and is null

Open in new window

Avatar of chapmandew
Flag of United States of America image

I believe I worked w/ you on this earlier in the're not setting any criteria on the fields other than making sure they are not null..don't you think you shoudl add some more criteria to the address fields?
Avatar of svs1919


ya i asked a question similar to this but that was for matching records. This is for unmatching records and now the problen is i cant say c.street1 <> b.address2 bcos this in a subquery. and if even 1 column matches i am not getting the records. Can you help me out in this
You aren't getting those records because you just set criteria to avoid rows with NULL in Address1, Address2, state and zip fields.

Don't you think should be better try something like this:
AND (c.street1 <> b.address1 OR c.street2 <> b.address2 OR ...)
Avatar of svs1919


i will try this but can we use <> in a sub query as some one told me thats  not a right way to do that
Avatar of svs1919


Ad also there are chances of having multiple addresses for 1 record  so we just have to compare with
the matching record
Avatar of jaryco
Flag of Costa Rica image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account