searching a sql server table based upon another table
-
Hi,
I have a bit of a problem
with a query I am trying to write.I have two tables
TABLE A
Description varchar (160)
Serviceid int
TABLE B
serviceID int
keyword varchar(160)
what I want to do is search the description of table A so that it returns any records for a serviceid where it starts with a keyword in TABLE B for the same service ID
I also need to make sure that when a match occurs, that there is not a more relevent match for a different serviceid, so for example:
TABLE A
tomas 1
TABLE B
1 tom
2 tomas
In this case, if I perform the search on serviceid 1 then it should return 0 records as even though tomas starts with tom, there is a more relevent match on servicid 2.
I have oversimplified my problem quite a lot but it takes about 8 seconds to do a search that ignores that there may be a more relevent match so don't really want to have a lot of iterations if possible as i can't afford the cost.
I hope this makes sense as I am pants at explaining things.
-
The solution to that problem is quite easy, can you post the ACTUAL NON SIMPLIFIED problem so that we don't do the work twice??
-
I think this 'may' be what you are looking for, (I added some extra data just to make sure):
DECLARE @TableA TABLE( [Description] varchar(10), -- 160
ServiceID integer)
INSERT INTO @TableA VALUES( 'tomas', 1)
INSERT INTO @TableA VALUES( 'tomass', 2)
INSERT INTO @TableA VALUES( 'tumas', 3)
INSERT INTO @TableA VALUES( 'toumas', 4)
INSERT INTO @TableA VALUES( 'tomaas', 5)
DECLARE @TableB TABLE( ServiceID integer,
KeyWord varchar(10)) -- 160
INSERT INTO @TableB VALUES( 1, 'tom')
INSERT INTO @TableB VALUES( 2, 'tomas')
INSERT INTO @TableB VALUES( 3, 'tamas')
INSERT INTO @TableB VALUES( 4, 'timas')
INSERT INTO @TableB VALUES( 5, 'tomass')
INSERT INTO @TableB VALUES( 6, 'tumas')
SELECT A.ServiceID, A.[Description], B.ServiceID, B.KeyWord
FROM @TableA A
INNER JOIN @TableB B ON( A.[Description] LIKE B.KeyWord
AND DIFFERENCE( A.[Description], B.KeyWord) = 4)
I wasn't born stupid - I had to study.
-
SELECT A.ServiceID, A.[Description], B.ServiceID, B.KeyWord
FROM @TableA A
INNER JOIN @TableB B ON( A.[Description] LIKE B.KeyWord
AND DIFFERENCE( A.[Description], B.KeyWord) = 4)
Wouldn't you want a '%' tacked on to the end of that inner join line as in
INNER JOIN @TableB B ON( A.[Description] LIKE (B.KeyWord + '%')
?
Also, you want to eliminate cases where there are two or more similar records correct? What if you returned the count(*) of matching records as a column (grouping on the A.Description column I think {closed your original post - might change that to the B.keyword column}) and do some sort of having count(*) = 1. You'll need to join this back to your original query as above to just get those descriptions and keywords that match appropriately.
-
Hi RGR, when I said that I have simplified this problem I meant in the fact that I have removed a lot of joins etc to get to these two tables. I was just emphasising that my query is quite slow because of the amount of data so I couldn't do itterative searches or too many subqueries.
Ian & Farrell, thanks for that I will try it at work this morning. I have never used the description function before so will be reading up on it. Just to clarify, I dont want to eliminate similar keywords, what I want to do I select the most relevent keyword, ie the keyword that has the most letters matching, so for the description 'tomas trubadour', tom has a match of 3 letter, where tomas is 4 so is more relevent and should be the keyword chosen.
Thanks for all your help so far, I don't really use forums and am suprised
at the quick response.
-
I've just checked out the Difference function (sorry refered to it as the description function in my last post) and I dont think i can use it as I am not working with words sounding the same. The database is to be used for people sending in sms messages and I want to process them based on a keyword at the start of a message.
So In my first example I dont want to process a message 'tomas smells' with serviceid 1 as serviceid 2 has a more relevent (more matching characters) keyword.
When I perform a search for all messages that are in serviceid1 then I would expect the message 'tomas smells' not to be returned
When I perform a search for all messages that are in serviceid2 then I would expect the message 'tomas smells' to be returned.
I may also have a keyword of '' and serviceid 3 in TABLE A which should return all messages that haven't got a more relevent keyword for one of the other services. So If i Want all messages for serviceid 3, then the whole database will be returned minus records starting with tom and tomas.
-
You lost me. I think you posted only a portion of the text within your [Description] column. This lead Ian, (good addition to my code) and I to believe you were matching on individual words.
Could you give us more information as to what is really contained within your columns? And are you matching in both directions or is KeyWord the main word to use for finding a match?
Also, are you only matching on the first word in the [Description] column, or any word.
I think we need a more thorough explanation of your desires here...
I wasn't born stupid - I had to study.
-
Yeah tis getting a bit tricky. Which way is the match going
I got confused - although lack of sleep lately isn't helping! 
Please give a few clear examples with meaningful table names and some sort of background to help it all click into place.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply