Comparing two tables for matching items 1

[DrumAt5280]

I have two DB tables, one table has a complete list of street names and the other has partial list of street names - I would like to make sure that each street name in the Partial list has a matching entry in the Complete list.

Because i have thousands of street names how can i automate this matching process with the two tables?

Here is what i am thinking (I will be using Coldfusion):

I create a loop,
Inside this loop i query the first item in the Partial list,
Assign the returned record to a variable such as "Partial-result",
Then query the Complete list to see if it matches the "Partial-result" variable - if it does start the loop process again - if not then write that variable to a new table called "NoMatch" and start the loop again.

Does this logic above seem to be the best way to do it?

Thanks!

 

[DotNetGnat]

i would suggest you to do this at the query level...

-DNG

 

[TonyGroves]

It's simpler than you might think! How about:
[tt]
SELECT partiallist.streetname
FROM
partiallist
LEFT JOIN completelist USING (streetname)
WHERE completelist.streetname IS NULL
[/tt]

 

[DrumAt5280]

Cool, so it looks like i don't have to do a loop at all by just using a LEFT JOIN - cool!

Oh, wait - it looks like that the query above is still within a loop - is that correct?

 

[TonyGroves]

It's a single query, which returns at once all the non-matching records. It's then up to your program to process all the records.

 

[DrumAt5280]

Wow, i just used the query and it worked great. Thanks and you saved me a ton of time doing it by hand.