Syntax to create cursor from 2 tables

[johann59]

I need to write a SQL statement that can merge duplicate field(s) and create single records into a Cursor

For example:

Table1 (Main)
First,Last,Name,Address,City,State,ZipCode
1. Winston,Smith,Winston Smith,123 Anywhere St,Chicago,IL,60601
2. Andrea,Lopez,Andrea Lopez,235 N Ave,Lawrence,NJ,08648
3. Sandra,Johnson,Sandra Johnson,2500 8th St,Los Angeles,CA,90061
4. Kate,Wilson,Kate Wilson,987 M Blvd,Tarzana,CA,91356

Table2 (Duplicates)
First,Last,Name,Address,City,State,ZipCode
1. Robert,Smith,Robert Smith,123 Anywhere St,Chicago,IL,60601
2. Jami,Smith,Jami Smith,123 Anywhere St,Chicago,IL,60601
3. Brit,Smith,Brit Smith,123 Anywhere St,Chicago,IL,60601
4. Justin,Abe,Justin Abe,123 Anywhere St,Chicago,IL,60601
5. John,Lopez,John Lopez,235 N Ave,Lawrence,NJ,08648
6. Jane,Hernandez,Jane Hernandez,235 N Ave,Lawrence,NJ,08648
7. Kim,Eyre,Kim Eyre,987 M Blvd,Tarzana,CA,91356

In this case records Table1 (rec 1) matches table2 (rec 1, 2, 3 and 4) because of the same address, city st & zip.
Table1 (rec 2) matches table2 (rec 5 & 6)
Table1 (rec 3) does not have a match
Table1 (rec 4) matches table2 (rec 7)

The cursor would result in
Name1 Name2 Name3 Name4 Name5 Address City State ZipCode
1. Winston Smith Robert Smith Jami Smith Brit Smith Justin Abe 123 Anywhere St Chicago IL 60601
2. Andrea Lopez John Lopez Jane Hernandez 235 N Ave Lawrence NJ 08648
3. Sandra Johnson 2500 8th St Los Angeles CA 90061
4. Kate Wilson Kim Eyre 987 M Blvd Tarzana CA 91356

and so on...
Up to 6 or 7 names for the same address.

I did make a key field (match1) (See image) on both tables, so I can find the matching records from table2, but I cannot seem able to write the correct SQL statement to create the cursor from both tables.

I have been at it for a few days and have not been able to create it. Pointing me in the correct direction is appreciated.

It might be a very simple answer but one I admit I have not been able to find.

Thanks.

Using VFP 9

 

[mjcmkrsr]

Hi,
Below a sketch of how you could achieve this - a two step approach

LOCAL lcName, lcZipCode

CREATE CURSOR tblNames (cName C(20), cStreet C(20), cCity C(20), cZipCode C(5))

INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Winston Smith","123 Anywhere St","Chicago","60601")
INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Andrea Lopez","235 N Ave","Lawrence","08648")
INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Sandra Johnson","2500 8th St","Los Angeles","90061")
INSERT INTO tblNames (cName, cStreet, cCity, cZipCode) VALUES ("Kate Wilson","987 M Blvd","Tarzana","91356")

CREATE CURSOR tblDupeNames (cName C(20), cStreet C(20), cCity C(20), cZipCode C(5))

INSERT INTO tblDupeNames (cName, cStreet, cCity, cZipCode) VALUES ("Robert Smith","123 Anywhere St","Chicago","60601")
INSERT INTO tblDupeNames (cName, cStreet, cCity, cZipCode) VALUES ("Jami Smith","123 Anywhere St","Chicago","60601")
INSERT INTO tblDupeNames (cName, cStreet, cCity, cZipCode) VALUES ("Brit Smith","123 Anywhere St","Chicago","60601")
INSERT INTO tblDupeNames (cName, cStreet, cCity, cZipCode) VALUES ("Justin Abe","123 Anywhere St","Chicago","60601")
INSERT INTO tblDupeNames (cName, cStreet, cCity, cZipCode) VALUES ("John Lopez","235 N Ave","Lawrence","08648")
INSERT INTO tblDupeNames (cName, cStreet, cCity, cZipCode) VALUES ("Jane Hernandez","235 N Ave","Lawrence","08648")
INSERT INTO tblDupeNames (cName, cStreet, cCity, cZipCode) VALUES ("Kim Eyre","987 M Blvd","Tarzana","91356")

SELECT cName, cStreet, cCity, cZipCode FROM tblNames ;
	UNION ALL ;
	SELECT cName, cStreet, cCity, cZipCode FROM tblDupeNames ;
	ORDER BY cZipCode ;
	INTO CURSOR crsAllNames

CREATE CURSOR crsCompNames (cName C(100), cStreet C(20), cCity C(20), cZipCode C(5))

SELECT crsAllNames

LOCATE 

lcZipCode = cZipCode
lcName = ""

DO WHILE !EOF() 

	SCAN WHILE cZipCode = lcZipCode
		lcName = IIF(EMPTY(ALLTRIM(lcName)), cName, ALLTRIM(lcName)+", "+ cName)
		lcStreet = cStreet
		lcCity = cCity
	ENDSCAN 
	
	INSERT INTO crsCompNames VALUES (lcName, lcStreet, lcCity, lcZipCode)

	lcZipCode = cZipcode
	lcName = ""

ENDDO 

SELECT crsCompNames

BROWSE 

CLOSE ALL
CLEAR ALL

hth
MarK

 

[Olaf Doschke]

I'd go this route, creatuing an Addresses Table, then a PErsons table:

Cd Getenv("TEMP")
Create Database dbcAddresses

Create Table tblNames (cName C(20), cStreet C(20), cCity C(20), cZipCode C(5))
Create Table tblDupeNames (cName C(20), cStreet C(20), cCity C(20), cZipCode C(5))

Create Table tblAddresses (iid Int Autoinc, cStreet C(20), cCity C(20), cZipCode C(5), Primary Key iid Tag pid)
Index On cStreet+cCity+cZipCode Candidate Tag xUnique
Create Table tblPersons (iid Int Autoinc, cName C(20), iAddressid Int, Foreign Key Tag fAdr References tblAddresses Tag pid, Primary Key iid Tag pid)
Index On cName Candidate Tag xUnique

Insert Into tblNames (cName, cStreet, cCity, cZipCode) Values ("Winston Smith","123 Anywhere St","Chicago","60601")
Insert Into tblNames (cName, cStreet, cCity, cZipCode) Values ("Andrea Lopez","235 N Ave","Lawrence","08648")
Insert Into tblNames (cName, cStreet, cCity, cZipCode) Values ("Sandra Johnson","2500 8th St","Los Angeles","90061")
Insert Into tblNames (cName, cStreet, cCity, cZipCode) Values ("Kate Wilson","987 M Blvd","Tarzana","91356")

Insert Into tblDupeNames (cName, cStreet, cCity, cZipCode) Values ("Robert Smith","123 Anywhere St","Chicago","60601")
Insert Into tblDupeNames (cName, cStreet, cCity, cZipCode) Values ("Jami Smith","123 Anywhere St","Chicago","60601")
Insert Into tblDupeNames (cName, cStreet, cCity, cZipCode) Values ("Brit Smith","123 Anywhere St","Chicago","60601")
Insert Into tblDupeNames (cName, cStreet, cCity, cZipCode) Values ("Justin Abe","123 Anywhere St","Chicago","60601")
Insert Into tblDupeNames (cName, cStreet, cCity, cZipCode) Values ("John Lopez","235 N Ave","Lawrence","08648")
Insert Into tblDupeNames (cName, cStreet, cCity, cZipCode) Values ("Jane Hernandez","235 N Ave","Lawrence","08648")
Insert Into tblDupeNames (cName, cStreet, cCity, cZipCode) Values ("Kim Eyre","987 M Blvd","Tarzana","91356")

Insert Into tblAddresses (cStreet, cCity, cZipCode) Select cStreet, cCity, cZipCode From tblNames Union (Select cStreet, cCity, cZipCode From tblDupeNames)

Insert Into tblPersons (cName, iAddressid) ;
   Select N1.cName, A1.iid As iAddressid From tblNames N1 ;
      Inner Join tblAddresses A1 On N1.cStreet+N1.cCity+N1.cZipCode=A1.cStreet+A1.cCity+A1.cZipCode ;
Union (;
   Select N2.cName, A2.iid As iAddressid From tblDupeNames N2 ;
      Inner Join tblAddresses A2 On N2.cStreet+N2.cCity+N2.cZipCode=A2.cStreet+A2.cCity+A2.cZipCode)

Olaf Doschke Software Engineering

https://www.doschke.name

 

[johann59]

Thanks Mark, but I have a 70,000 record table to match against the 8000 duplicates. How can I programmatically match the two tables? Using the INSERT will not work with that big of a table.

And that is on me, I did not say at the beginning the amount of records that I had. Sorry for that.

 

[mjcmkrsr]

Hi

... How can I programmatically match the two tables?

You'll just have to create the matching conditions, .e.g.

SELECT cName, cStreet, cCity, cZipCode FROM tblNames ;
	UNION ALL ;
	SELECT cName, cStreet, cCity, cZipCode FROM tblDupeNames ;
	[highlight #FCE94F]ORDER BY cZipCode, cCity, cStreet[/highlight] ;
	INTO CURSOR crsAllNames

Using the INSERT will not work with that big of a table.

Why? Did you try? Or do I miss something? If you have too many "Names" you might want to use a Memo field instead of a C

CREATE CURSOR crsCompNames [highlight #FCE94F](cName M[/highlight], cStreet C(20), cCity C(20), cZipCode C(5))

hth
MarK