HELP Joing table by string searches??!!
-
I need to join the following two tables by one column that contains the property name in another.
For example:
In table A, the property name is Entered as 'Stain Hills'' but in Table B its 'Chastain (DBA Stain Hills)'.
Another Example would be, In Table A, the property name is 'Winding Hills' but in Table B its 'Winding Hills - Miles'
How would i go about doing that, i though it might be something like:
select * from table a, table b Where tableb.property contains '%tablea.property%'
or something like that? I cannot seem to get it to work though!
Please help!
Thanks in Advance!
-
How about this:
select * from tablea
INNER JOIN tableb ON tableb.property LIKE '%tablea.property%'
PS. Changing CONTAINS to LIKE should do it. I just don't like the old linking syntax anymore.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Thanks!
-
Almost
Instead of
select * from tablea
INNER JOIN tableb ON tableb.property LIKE '%tablea.property%'
Try
select * from tablea
INNER JOIN tableb ON tableb.property LIKE '%' + tablea.property + '%'
Note that it will perform like a dog if you have lots of rows as there won't be any index use. If it is something you are doing on a continuous basis, but the tables themselves do not change, consider a table that you keep up to date with triggers that has the join materialised.... (an indexed view might do the trick too although I don't know how efficiently SQL would keep it up to date with that table join using LIKE)
-
No matter what I do I always mess up the syntax
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply