Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
scottdg
Starting Member
20 Posts |
Posted - 2013-06-19 : 13:37:28
|
Hi all,I need to add a table to my view from a separate database. It seems as if I can do this with the following syntax:table1 JOIN database2.DBO.tablename on....and this is where I am not sure. I am searching for products and each product has an ID#. The problem is they are not exactly alike but they are similar. The original product in table 1 was a live seminar and has a product code like A123456/ABCD, A. That live seminar was then turned into a video that has a product code like ON123456. How do I join the tables based on a partial product code match? Thanks in advance,Scott |
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-06-19 : 13:42:21
|
I would imagine you would just reformat the data so they will match using a String function like LTRIM. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-19 : 14:15:12
|
IF there are some rules that you can use to identify the similar parts, you could conceivably construct a query. For example, if you knew the product code is always two letters followed by the part that would be in the live seminar, then you could do the following:WHERE a.productcode LIKE '%'+STUFF(b.productcode,1,2,'')+'%' Here a is the table that contains the live seminar data and b is the table that has the product code data.If you cannot find any rules like that you would need to use full-text search http://msdn.microsoft.com/en-us/library/ms142571.aspx But you would need to do some preparation work before you can use full text search. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-06-19 : 14:29:50
|
I would think that the best solution would be to keep the base productid in its own column and any modifiers (e.g., "A", "ON", etc) in a separate column(s) (prefix, suffix). Then the join becomes simple.=================================================The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen |
|
|
scottdg
Starting Member
20 Posts |
Posted - 2013-06-19 : 15:08:36
|
quote: Originally posted by Bustaz Kool I would think that the best solution would be to keep the base productid in its own column and any modifiers (e.g., "A", "ON", etc) in a separate column(s) (prefix, suffix). Then the join becomes simple.=================================================The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
You are absolutely right but that isn't really an option at this point.As far as the other response about the product codes; they are not uniform in size. Here is a small sampling of products. This is actually only 3 seminars but because of continuing education credits there are several products accounting for each type of credit:S179714546/CLE_NYPS179714546/CLE_PASS179814547/CLE_CFPS179814547/CLE_CPES179814547/CLE_MGS179814547/CLE_NYPS179814547/CLE_PASS179814851/CLE_MGS179814851/CLE_NYPthe video equivalent of this would be ON179814851. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-06-19 : 16:23:23
|
Can you delineate how a productid gets assembled? Are the components of a predictable size? (e.g., 1 or 2 character prefix, x digits, variable length suffix?)=================================================The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen |
|
|
|
|
|
|
|