comparing postcodes in one table to validation table

Question

Post reply

comparing postcodes in one table to validation table

charlie-337681

SSC Enthusiast

Points: 179
More actions
August 26, 2007 at 7:43 pm

#118324

Hi all
I have a customer table with a postcode and a suburb fields and cutomer info which is manually entered by data entry people...
I am trying to compare the entries against a postcode table with the correct postcodes which have fields postcode and suburb and based on the postcode entered in the customer table it should be the same as the suburb in the postcode table, if they are not the same output them to a table for manual checking..How would I go about this

thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Something like this...

SELECT c.*,z.*

FROM Customer c

LEFT OUTER JOIN PostCodeTable z

ON c.PostCode = z.PostCode

AND c.Suburb = z.Suburb

WHERE z.PostCode IS NULL

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 2

Can you give us an idea of how you store these items in the tables? Jeff has a good suggestion, but it's hard to know if a left join is needed.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

charlie-337681 SSC Enthusiast Points: 179 More actions · Answer 3

Steve,

customer table consists off

customername varchar

customerphoneno varchar

postcode varchar

suburb varchar

The Postcode table is a table which I downloaded from the postoffice which contains all the valid postcodes in australia with postcode,locality in varchar

customer details are entered in manually with the postcode and surburb, i would like to compare the entries are correct based on the postcode with the suburb in the postcode table

Jeffs suggestion works btw but is there some other way ?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

There are, in fact, other ways using correlated subqueries in conjunction with IF EXISTS where, in the presence of correct indexes, can sometimes be made a bit faster than the outer join method I suggested. Sometimes, again, depending on the nature of the beast and the indexes present, WHERE NOT IN can be very fast as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions