SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with a join


Help with a join

Author
Message
TSQL Tryer
TSQL Tryer
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 708
Hi there,

I am trying to join two tables that visibly doesn't have any thing unique as they come from separate systems. I believe I have a solution but am not sure how to write the TSQL to achieve what I'm after.

Database 1

We have a "Unique Transaction ID".

Examples being -

1. 4461
2. 14130
3. 23891

Database 2

When these transactions are imported into another system these change to the following -

1. CP000000004461
2. CP000000014130
3. CP000000023891

So I need to find a way in my first table to make 4461 to CP000000004461 and 23891 to CP000000023891, so that I can use that as a unique link to link to the second table.

It's the leading zeroes I'm having issues with but the length will always be 14.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18318 Visits: 20411
How about something like this?

declare @x table (TransId varchar(10))

insert @x
(TransId)
values ('4461'),
('14130')

select TransId
,'CP' + right('000000000000' + TransId, 12)
from @x x




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15009 Visits: 18592
Quick thought, this can easily be stuffed
Cool

;WITH BASE_ID AS
( SELECT BID FROM
(VALUES (4461),(14130),(23891)) AS X(BID)
)

SELECT
BI.BID
,STUFF('CP000000000000'
,14 - LEN(CAST(BI.BID AS VARCHAR(12)))
,LEN(CAST(BI.BID AS VARCHAR(12)))
,CAST(BI.BID AS VARCHAR(12))
) AS UNIQUE_LINK

FROM BASE_ID BI



Results
BID         UNIQUE_LINK
----------- ---------------
4461 CP000000044610
14130 CP000000141300
23891 CP000000238910

niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 226
Recently I have faced this kind of situation in my organization. Temp table and with cte is the best option in my opinion when there is no relation between the tables or unique values.
TSQL Tryer
TSQL Tryer
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 708
Thanks guys for the help so far.

I've discovered that what I thought was a potential link actually isn't so after all your advice it's not going to help me in this scenario.

That being said I think I have found another potential to link my two data sets together.

Example I table 1 we have "708698"

In table 2 we have "Internet Card Payment Auth Code: 708698"

Is there anyway in SQL that you could say join 708698 to Internet Card Payment Auth Code: 708698 by looking in the table 2 field to match the 708698?
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39127 Visits: 38518
Ryan Keast (8/21/2014)
Thanks guys for the help so far.

I've discovered that what I thought was a potential link actually isn't so after all your advice it's not going to help me in this scenario.

That being said I think I have found another potential to link my two data sets together.

Example I table 1 we have "708698"

In table 2 we have "Internet Card Payment Auth Code: 708698"

Is there anyway in SQL that you could say join 708698 to Internet Card Payment Auth Code: 708698 by looking in the table 2 field to match the 708698?


Is all the data in the column you wish to join to like this: Internet Card Payment Auth Code: nnnnnn where nnnnnn is the value to be joined?

If yes, can you add a persisted computed column to table 2 that extracts the Internet Card Payment Auth Code: ? If so, you could then index the persisted column and join to it.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search