May 26, 2016 at 7:12 am
Hi
I had a problem with joining two tables but not based on specific unique code. Ideally I don't expect this kind of problems to occur if the database design is correct however this question was asked by somebody so wanted to share and understand different ways of doing this query. I wanted to join both tables so that I can get full code_name like table2 in table1. The first thing we need to do is change varchar(20) to varchar(50) for longer string.
One thing we could do perhaps is take the length of code_name and then match code_names from both tables and once you find the match update it. However there is no other unique identifier to join these tables as one code could have two names. The code name is just for reference, in the database it is purpose of the clinic so it could be difference purpose for the same clinic.
Any help will be appreciated.
CREATE TABLE table1 (id INT, code VARCHAR(10), code_name VARCHAR(20))
INSERT INTO table1
SELECT 1, 'C002', 'Purpose of Cardiovas'
UNION
SELECT 2, 'C002', 'Purpose of Cardio'
Union
SELECT 3, 'C003', 'Purpose of Heart Byp'
CREATE TABLE table2 (id INT, code VARCHAR(10), code_name VARCHAR(50))
INSERT INTO table2
SELECT 1, 'C002', 'Purpose of Cardiovascular Surgery'
UNION
SELECT 2, 'C002', 'Purpose of Cardio'
Union
SELECT 3, 'C003', 'Purpose of Heart Bypass'
SELECT * FROM dbo.table1
SELECT * FROM dbo.table2
May 26, 2016 at 7:26 am
You don't need table1 at all, do you, since everything is contained in table2? You didn't specify expected results, but here's a guess:
WITH Lengths AS (
SELECT
code
,code_name
,ROW_NUMBER() OVER (PARTITION BY code ORDER BY LEN(code_name) DESC) RowNo
FROM table2
)
SELECT
code
,code_name
FROM Lengths
WHERE RowNo = 1
John
May 26, 2016 at 7:27 am
something like this gets the desired code name, but which would you want with multiple matches?
SELECT row_number() over(partition By T2.code_name ORDER BY T2.code) AS RW,T2.code_name As DesiredCodeName,T1.*
FROM dbo.table1 T1
INNER JOIN dbo.table2 T2
ON T1.code = T2.code
AND CHARINDEX(T1.code_name,t2.code_name) > 0
ORDER By T1.id,T1.code,RW
Lowell
May 26, 2016 at 7:43 am
Lowell (5/26/2016)
something like this gets the desired code name, but which would you want with multiple matches?
SELECT row_number() over(partition By T2.code_name ORDER BY T2.code) AS RW,T2.code_name As DesiredCodeName,T1.*
FROM dbo.table1 T1
INNER JOIN dbo.table2 T2
ON T1.code = T2.code
AND CHARINDEX(T1.code_name,t2.code_name) > 0
ORDER By T1.id,T1.code,RW
I was thinking something along the lines of
SELECT row_number() over(partition By T2.code_name ORDER BY T2.code) AS RW,T2.code_name As DesiredCodeName,T1.*
FROM dbo.table1 T1
INNER JOIN dbo.table2 T2
ON T1.code = T2.code
AND t2.code_name LIKE t1.code_name + '%'
ORDER By T1.id,T1.code,RW
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 26, 2016 at 9:12 am
Thanks for the response. Just to clarify table 2 contains correct information however both of these tables are on different servers and I would like to update only table 1 where code_name (first 20 characters) matches with table 2. I couldn't transfer the table from one place to another as some work has been done on table 2 however there is a problem with code_name fields which needs updating based on length.
May 26, 2016 at 9:23 am
Well, you could fiddle about with an SSIS package or linked servers or similar wizardry, but I think the way I'd do it is to build the INSERT script on server 2, and run it on server 1 after clearing the table.
John
May 26, 2016 at 9:25 am
This one works however it gives multiple matches which is not expected. If the table 1 contains less than 20 characters then it will stay as it is and there is a match in table 2 for the same (i.e. purpose of cardio). The reason this is happening is because new server has length of field which is 20 characters and old server already had length which was 50 characters.
SELECT row_number() over(partition By T2.code_name ORDER BY T2.code) AS RW,T2.code_name As DesiredCodeName,T1.*
FROM dbo.table1 T1
INNER JOIN dbo.table2 T2
ON T1.code = T2.code
AND CHARINDEX(T1.code_name,t2.code_name) > 0
ORDER By T1.id,T1.code,RW
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply