Is there any way to use LIKE in JOINS?
-
Hi, I'm trying to use some keywords from a field in one table as a filter to another table. I've succeeded in writing some dynamic SQL that loops through each keyword and writes a WHERE clause similar to this:
- WHERE
- mt.description LIKE '%lead%' OR
- mt.description LIKE '%chromium%' OR ...
It works but there can be hundreds of keywords. I'd rather be able to use some sort of join between my main table and the keywords table. Something like this is what I have in mind:
- SELECT mt.description, kw.keyword
- FROM MainTable mt
INNER JOIN KeyWords kw
- ON mt.description LIKE '%' + kw.keyword + '%'
- This example does not work...Is there something like this that does work? Am I stuck using my dynamic sql version?
- I'm on SQL Server 7.0.
-
Simple answer: No
Maybe something from this site will help you with your query:
Especially the articles on dynamic searches and dynamic sql might be helpful
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank,
It's as I feared. I'll have to proceed with my dynamic sql method. Too bad, I was already thinking of lots of uses for it if this was possible...
The link you gave looks like really good reading, thanks for it!
Greg.
-
I don't see why such a query wouldn't work. The following works fine for me on both SQL 2000 and 7.0.
use pubs
select t.title, a.state
from titles t
inner join authors a on t.title like '%' + a.state + '%'
When you say it doesn't work, what error are you getting? Are you sure your keyword is varchar?
Cheers,
- Mark -
Learn each day something new
Your query works here fine, too, Mark.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'm testing Microsoft Reporting Services and using the database provided, this works:
SELECT DISTINCT a.AddressId, a.AddressLine1, a.City
FROM Address a
INNER JOIN Address b ON a.City LIKE '%Ken%' AND b.City LIKE '%ken%'
Not sure what uses it would have, can you elaborate Greg ?
Measure twice, cut once
-
Same thing but using PATINDEX
SELECT mt.description, kw.keyword
FROM MainTable mt
INNER JOIN KeyWords kw
ON PATINDEX ('%' + kw.keyword + '%',mt.description) > 0
Far away is close at hand in the images of elsewhere.
Anon. -
We are using this very thing on SQL Server 2000 (Microsoft SQL Server 2000 - 8.00.760) to do a join on two "Hash" keys that we use to query based on a person's hierarchy in the company.
For example a person coming in at a region level would have a hash of 1-100-12 (corporate-division-region) and we wanted to find all dealers in his region (as well as all of the people, who work at the dealers).
Examples: 1-100-12-123, 1-100-12-124, 1-100-12-134, etc. The join looked something like this
select * from hierarchy h
inner join dealers d on h.haskey like (d.hashkey + '%')
This works very good. Hope this helps...
Anton
-
If you only want one row per description in the result set, regardless of the number of keywords found, something like the following might work:
SELECT mt.description
FROM MainTable mt
WHERE EXISTS (SELECT *
FROM KeyWords kw
WHERE mt.description LIKE '%' + kw.keyword + '%')
ORDER mt.description
Or, use a SELECT DISTINCT:
SELECT DISTINCT mt.description
FROM MainTable mt
INNER JOIN KeyWords kw
ON mt.description LIKE '%' + kw.keyword + '%'
ORDER BY mt.description
Otherwise, I agree with the others, your code worked as presented.
Mike
-
Great posting and dialouge. If you need to join a comma list to an Id (int) field, you just have to convert the ID field to varchar. There should be a part 3 to the article on complex joins discussing the issues surrounding using like in a join. Any performance issues?
Example where AGLs is a comma list of IDs. I add commas leading and trailing to avoid mismatching 1 and 10, etc.
SELECT ER_ID,G_L_ID,G_L
FROM
A_I
INNER JOIN G_L
ON (',' + replace(A_I.AGLs,' ','') + ',')
LIKE '%,' + CONVERT(nvarchar(20),G_L_ID) + ',%'
-
Great example but bad performance compared to a dynamic sql.
Using your example with LIKE does a table scan which is very costly.
Using the IN directive do an INDEX SEEK if the field is an index (as in your example) and it is way faster.
Looks like doing a dynamic SQL statement is the way to go.
-
Hi everyone, first off, thanks for all the feedback! I'm glad to know that it works, it seemed like it should have to me.
1) Mark, Anton...Solved my problem. I had reversed the LIKE in my actual query from how I posted it! My result set always returned 0 records. Go figure that I posted the SQL correctly here yet had it messed up in my real query and couldn't spot the difference yesterday...
2) edafe...I'm using this to link chemical keywords to a description field in the main table. In an ideal database all entries in the main table would use a key number to uniquely identify them...but this is far from that ideal database!
3) sdude...While the performance will be worse using the LIKE join, the upside is that I can use other fields from my keyword table in the result set, worth it in my case. On a desktop edition SQL installation with 600k rows in the main table, going aginst 50 keywords was about a 10 sec query to return 2500 records. Certainly acceptable for my system.
4) Everyone else...Thanks for your posts! I'll be playing around with some of the other suggestions to see if I net any boost in performance.
A sidenote...GRRR--my responses keep disappearing when I post them...Is there a time limit between starting to type and posting successfully?
-
And the results...
SQL 7.0 desktop db installation on a development machine. Main table has 600k rows, keyword lookup using ~50 words. Query run from ISQLW.
PATINDEX and the LIKE JOIN take identical time to run, 12 sec, and return ~2500 rows. Dynamic SQL saves about 40% timewise, but since there's no join to the keywords table I lose showing them or related fields in my result set.
As expected, indexing the description field of the main table had no effect on performance.
So for my requirements I'm going to stick with the LIKE join.
Thanks again!
-
Hi Greg,
let me say sorry for the bad advice I posted. Last week I made several remarkable poor postings here
Hope quality will improve again when things calm down in privte life.
Glad to see you found a solution anyway.
----------------------------------------
Looks like doing a dynamic SQL statement is the way to go.
Most members here know, I have a different opinion on the use of dynamic sql. Read the links I posted before, and decide then if that's the way to go.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply