April 12, 2006 at 8:49 am
Hi Guys,
Sorry if I'm being a bit simple but I've been diving in and out of four different systems for weeks now to try and get an answer, I'm scraping the bottom of a particularly fetid barrel and I'm very very tired...
Simple problem, I'm trying to match two fields where the only primary key available is human input (yeah, yeah, don't ask...).
Here's an example of a "matching" field: 'A5075' matches 'A5075 - Violet Toner'
SELECT item.[search description], mat.[material__code]
FROM [Item] item INNER JOIN
DISPENSER.rx_impress.dbo.material mat ON mat.[material__code] LIKE (item.[search description] + '%')
You'll note I'm running this across a linked server to another SQL server - is this my problem or am I just being dumb?
Thanks
Rich
April 12, 2006 at 9:52 am
I am not sure what will be the performance with the linked server and if my approach is better than yours. I like your solution. Other than that I would try to select things from the linked server into the temp table and join locally to the temp table. Do I understand correctly that you are searching not for one [search description] but for the set of [search descriptions] stored in the Item table?
Regards,Yelena Varsha
April 12, 2006 at 9:53 am
Sorry - I realise now I didn't explain the problem, which is, of course, that it doesn't actually work.
Performance is irrelevant to me - I'm only doing it once....
April 12, 2006 at 11:12 am
SELECT item.[search description], mat.[material__code]
FROM [Item] item INNER JOIN
DISPENSER.rx_impress.dbo.material mat ON mat.[material__code] LIKE SUBSTRING(ITEM.[SEARCH DESCRIPTION], 1, LEN(MAT.]METERIAL__CODE)
April 12, 2006 at 2:09 pm
Hi,
Try This: you have to modify your field names accordingly. I also had both tables in one database, maybe you have to copy a linked server table first locally. Here are 2 solutions. Explanation: LIKE comparison is a string so you have to compose a dynamic string as '%' + Item.SearchDesc + '%' not '%Item.SearchDesc%'
----------------------------------------
---This Works-------
Select
item.SearchDesc,mat.material_code from Item join
Mat
ON mat.material_code LIKE '%' + Item.SearchDesc + '%'
---------------------------------------
---This Also Works----
Declare MyCursor Cursor
For
Select
SearchDesc from Item
Declare
@MyString varchar(50)
Create
Table ##TempTable (Sdesc varchar(50),Mcode varchar(50))
Open
MyCursor
Fetch
from MyCursor Into @MyString
while
(@@fetch_status = 0)
begin
Print @MyString
Insert ##TempTable
Select @MyString,material_code from
Mat
where material_code LIKE '%' + @MyString + '%'
Fetch Next from MyCursor Into @MyString
end
Select
* from ##TempTable
Close
MyCursor
Deallocate
MyCursor
Drop
Table ##TempTable
Regards,Yelena Varsha
April 13, 2006 at 4:55 am
It's no problem at all to use LIKE in a join, just be careful which side is LIKE'd with which.
It should be <longest value> LIKE <short value> + '%'
create table #x ( x varchar(20) not null )
create table #y ( y varchar(20) not null )
insert #x select 'A5075'
insert #y select 'A5075 - Violet Toner'
select *
from #x
join #y
on #y.y like #x.x + '%'
drop table #x, #y
go
x y
-------------------- --------------------
A5075 A5075 - Violet Toner
/Kenneth
April 13, 2006 at 6:39 am
Are you sure mat.[material__code] is of type varchar and that it doesn't contain trailing blanks? You might have to trim mat.[material__code] before appending the '%':
SELECT item.[search description], mat.[material__code]
FROM [Item] item INNER JOIN
DISPENSER.rx_impress.dbo.material mat ON mat.[material__code] LIKE (RTrim(item.[search description]) + '%')
April 19, 2006 at 2:46 am
Doh!!! That got it - cheers!!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply