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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Joining tables from different databases

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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_NYP
S179714546/CLE_PAS
S179814547/CLE_CFP
S179814547/CLE_CPE
S179814547/CLE_MG
S179814547/CLE_NYP
S179814547/CLE_PAS
S179814851/CLE_MG
S179814851/CLE_NYP

the video equivalent of this would be ON179814851.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -