Exclusion query

[jrottman]

Basically, I need to search the daily mls updates that we pull for any listing with our company codes, and compare that agaist our own database, to find out if we are missing any records in our own database that are listed in the mls.

I am not sure if this is the right way to go or not. Can someone give me a hand.


<cfquery name="Res" datasource="#dsn#">
select StreetNumDisplay, ZipCode, StreetName from tbl_IDX_Coded_RES
where OfficeList = 'CRIl01' and OfficeList = 'CRIl02' and OfficeList = 'CRIl03' and OfficeList = 'CRIl04'
</cfquery>


<cfloop query="Res" startrow="1" endrow="#Res.RecordCount#">
<cfquery name="hsStatusRes" datasource="#dsn#">
select * from status
where fld_houseNumber NOT IN '#Res.StreetNumDisplay#' and fld_zip NOT IN '#Res.ZipCode#' and fld_street NOT IN '%#Res.StreetName#%' and fld_archived = '0'
</cfquery>
</cfloop>

 

[r937]

a query inside a loop is never a good idea

two approaches (the first is preferred):

load the daily updates into your database, then the missing ones can be obtained by a single simple LEFT OUTER JOIN

or return both tables into CF and write a query-of-query


r937.com | rudy.ca