June 9, 2011 at 3:53 pm
Hi, I did try a few google searches and was unable to find exactly what I am looking for because most solutions relied on a join. I need to compare one table with 20,000 orgnames/orgnumbers's with another table that has just 50 org names using the like command. My goal is to identify what orgs from table2 already exist in table1.
The second table does not have any key value so I don't think I can use a join for this. I tried the following code:
select table1.orgnumber, table1.orgname
from table1 where table1.name like (select table2.orgname
from table2);
SQL tells me that I cannot have multiple values returned, but I don't know what else to do.
Thanks!
June 9, 2011 at 4:15 pm
You may want to check out EXCEPT and INTERSECT.
June 9, 2011 at 4:33 pm
Also joins are not constrained to keyed values only, so you can do a traditional join type operator. For joins key add lots to performance but with the data volumes you are mentioning it should not be noticable slow down. Try the join!
June 9, 2011 at 4:35 pm
but what would I join on? I need to use the like operator to compare the two strings and pull the rows that match.
-Danny
June 9, 2011 at 4:36 pm
but what would I join on? I need to use the like operator to compare the two strings and pull the rows that match.
-Danny
June 9, 2011 at 4:42 pm
hkflight (6/9/2011)
but what would I join on? I need to use the like operator to compare the two strings and pull the rows that match.-Danny
Not quite what you want but that should get you over the hump.
SELECT * FROM dbo.SmallTable ST LEFT OUTER JOIN dbo.BigTable BT ON '%' + BT.Orgname +'%' LIKE ST.Orgname WHERE BT.OrgName IS NOT NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply