View Single Post
  #7 (permalink)  
Old March 22nd, 2007, 04:20 AM
happygv happygv is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I am a bit confused with the explanation and the code that you posted in your original post.

Try this and see if this is what you were looking for?
Code:
INSERT INTO CompanyCompare 
-- Using Table1 as the base for comparision with Table2
Select Table1.ID, Table2.ID, Table1.Name, Table1.MaxValue 
    FROM table1, table2
    WHERE table1.name like '%' + table2.name + '%' AND Datalength(Table1.Name) = Datalength(Table2.Name)  
        AND Table1.ID NOT IN ( SELECT IDTab1 FROM CompanyCompare ) 
        AND Table2.ID NOT IN ( SELECT IDTab2 FROM CompanyCompare ) 
UNION 
Select Table1.ID, Table2.ID, Table1.Name, Table1.MaxValue 
    FROM table1, table2
    WHERE table1.name like '%' + table2.name + '%' AND Datalength(Table1.Name) <> Datalength(Table2.Name) 
        AND Table1.ID NOT IN ( SELECT IDTab1 FROM CompanyCompare ) 
        AND Table2.ID NOT IN ( SELECT IDTab2 FROM CompanyCompare ) 
UNION 
Select Table1.ID, Table2.ID, Table2.Name, Table1.MaxValue 
    FROM table1, table2
    WHERE table1.name like '%' + table2.name + '%' AND Datalength(Table1.Name) <> Datalength(Table2.Name) 
        AND Table1.ID NOT IN ( SELECT IDTab1 FROM CompanyCompare ) 
        AND Table2.ID NOT IN ( SELECT IDTab2 FROM CompanyCompare ) 

UNION 
-- Using Table2 as the base for comparision with Table1
Select Table1.ID, Table2.ID, Table2.Name, Table1.MaxValue 
    FROM table1, table2
    WHERE table2.name like '%' + table1.name + '%' AND Datalength(Table1.Name) = Datalength(Table2.Name)  
        AND Table1.ID NOT IN ( SELECT IDTab1 FROM CompanyCompare ) 
        AND Table2.ID NOT IN ( SELECT IDTab2 FROM CompanyCompare ) 
UNION 
Select Table1.ID, Table2.ID, Table1.Name, Table1.MaxValue 
    FROM table1, table2
    WHERE table2.name like '%' + table1.name + '%' AND Datalength(Table1.Name) <> Datalength(Table2.Name)  
        AND Table1.ID NOT IN ( SELECT IDTab1 FROM CompanyCompare ) 
        AND Table2.ID NOT IN ( SELECT IDTab2 FROM CompanyCompare ) 
UNION 
Select Table1.ID, Table2.ID, Table2.Name, Table1.MaxValue 
    FROM table1, table2
    WHERE table2.name like '%' + table1.name + '%' AND Datalength(Table1.Name) <> Datalength(Table2.Name)  
        AND Table1.ID NOT IN ( SELECT IDTab1 FROM CompanyCompare ) 
        AND Table2.ID NOT IN ( SELECT IDTab2 FROM CompanyCompare )
        This doesn't use Cursor, at the same time it doesn't insert duplicate values into CompanyCompare Table.

Hope this helps.
Cheers

_________________________
- Vijay G
Strive for Perfection
Reply With Quote