Link to home
Create AccountLog in
Avatar of svs1919
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
select
      *
From
      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 (
                  select
                        onumber,
                        count(*) as quantity
                  from
                        dbo.Lan
                  group by
                        oNumber
                 ) q on n.ONUMBER = q.onumber
      Left Outer Join block b (NOLOCK) On N.DEALER_ID = b.CUSTNMBR
Where
      n.ORDER_QUANTITY = q.quantity
and	b.address1 is null and  b.ADDRESS2 is null and  b.city is null and b.state is null and b.zip is null

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

I believe I worked w/ you on this earlier in the week...you'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
svs1919

ASKER

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

ASKER

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

ASKER

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

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