I have 2 tables that contain a column for a name part, i.e. John Doe has a row for John and one for Doe. The 2 tables have different names in them. I need to take the name part from one table and calculate the DIFFERENCE score to the name part in the other. Right now I'm using an inner and outer cursor to loop through each of the two tables. This works but is taking a long time to run.
Is there a different way that would speed this up?
An example of what I'm trying to do:
Table 1
Name
----
John
Doe
Jan
Smith
Table 2
Name
-----
Henry
Ford
Ransom
Eli
Olds
This is what I need:
DIFFERENCE('John','Henry')
DIFFERENCE('John','Ford')
DIFFERENCE('John','Ransom')
DIFFERENCE('John','Eli')
DIFFERENCE('John','Olds')
DIFFERENCE('Doe','Henry')
DIFFERENCE('Doe','Ford')
DIFFERENCE('Doe','Ransom')
DIFFERENCE('Doe','Eli')
DIFFERENCE('Doe','Olds')
DIFFERENCE('Jane','Henry')
DIFFERENCE('Jane','Ford')
DIFFERENCE('Jane','Ransom')
DIFFERENCE('Jane','Eli')
DIFFERENCE('Jane','Olds')
DIFFERENCE('Smith','Henry')
DIFFERENCE('Smith','Ford')
DIFFERENCE('Smith','Ransom')
DIFFERENCE('Smith','Eli')
DIFFERENCE('Smith','Olds')
Right now, I save these DIFFERENCE scores in a temp table for testing purposes. Sorry for the lack of formatting, I'm new to posting here. Thanks for you help.
you can use a cartesian join.. or CROSS JOIN.
SELECT DIFFERENCE(t1.Name, t2.Name)
FROM Table1 t1
CROSS JOIN Table2 t2