SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Joining tables from different databases
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scottdg
Starting Member

20 Posts

Posted - 06/19/2013 :  13:37:28  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 06/19/2013 :  13:42:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 06/19/2013 :  14:15:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1690 Posts

Posted - 06/19/2013 :  14:29:50  Show Profile  Reply with Quote
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 - 06/19/2013 :  15:08:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1690 Posts

Posted - 06/19/2013 :  16:23:23  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000