>I think I am going braindead trying to figure this out.
>
>I have to compare 47,000 records (I'll call BM) to 8,500 records (I'll call SD) to find all the possible matches between them. There maybe:
>a) no match in SD for each record in BM
>b) maybe a single match in SD for each record in BM
>c) maybe multiple matches in SD for each record in BM
>
>The end result is I need a table that contains each record in BM listing all the potential matches to SD (or no match all).
>
>I have 3 SQL statements that do the match and work independantly. I have tested each and they do as required. They are:
>
>--------------------------------------
>** read EXACT matches
>select * from bmdata left join sddmdata ;
> on left(&bmzip,5)=left(sddmdata.sdzip,5) ;
> where !empty(trim(sddmdata.sdaddr)) and ;
> upper(trim(&bmlname))=upper(trim(sddmdata.sdlname)) and ;
> upper(trim(&bmaddr))=upper(trim(sddmdata.addr)) into cursExact
>
>** read PROBABLE matches
>select * from bmdata left join sddmdata ;
> on left(&bmzip,5)=left(sddmdata.sdzip,5) ;
> where !empty(trim(sddmdata.sdaddr)) and ;
> upper(trim(&bmaddr))=upper(trim(sddmdata.addr)) into cursProb
>
>** read POSSIBLE matches
>select * from bmdata left join sddmdata ;
> on left(&bmzip,5)=left(sddmdata.sdzip,5) ;
> where !empty(trim(sddmdata.sdaddr)) and ;
> val(trim(&bmaddr))=val(trim(sddmdata.addr)) into cursPoss
>-----------------------------------------------------
>
>I believe my next step (or modifications to the above) is to combine these into a single cursor. The key here is hierachy. Keep in mind, I have stored the RECNO for each BM record into the cursors:
>
>- if a BM record exists in cursExact then keep that record and ignore any matches in cursProb & cursPoss for that BM record number
>- if a BM record exists in cursProb and not in cursExact, keep that record and ignore any matches in cursPoss for that BM record number
>- if a BM record exists in cursPoss and not in cursProb or cursExact, keep that record for that BM record number
>
>
>
>When I have the ending cursor, I need to then finally compare the cursor with the original BM table:
>
>- if the BM record (by recno()) has no match in the end cursor, then keep the BM record
>- if the BM record (by recno()) has at least one match in the end cursor, delete the orginal record in BM and add ALL the matching records from the end cursor.
>
>
>No matter what, the ending table (BM) will have at least the original records with no matches, or most likely, a list of all the non-matches and matches. Those records will then be used in a grid grouping on the original BM number.
>
>
>
>
>I hope this all makes sense - probably why it is driving me nuts!!! HELP!!!

Peter,

I think you can use LEFT JOINS on SD to get the desired results:

SELECT * ;
FROM bmdata ;
	LEFT JOIN sddmdata sd1 on left(sd1.zip,5) = left(&bmzip,5) ;
		and !empty(trim(sd1.sdaddr)) ;
		and upper(trim(&bmlname))=upper(trim(sd1.sdlname)) ;
		and upper(trim(&bmaddr))=upper(trim(sd1.addr)) ;
	LEFT JOIN sddmdata sd2 on left(sd2.zip,5) = left(&bmzip,5) ;
		and !empty(trim(sd2.sdaddr)) ;
		and upper(trim(&bmaddr))=upper(trim(sd2.addr)) ;
	LEFT JOIN sddmdata sd3 on left(sd3.zip,5) = left(&bmzip,5) ;
		and !empty(trim(sd3.sdaddr)) ;
		and val(trim(&bmaddr))=val(trim(sd3.addr))

I did not test this but it should get you on the right track.

- Keith