Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with a join Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2014 7:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 19, 2014 4:23 AM
Points: 218, Visits: 442
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.
Post #1605423
Posted Wednesday, August 20, 2014 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 5,101, Visits: 11,904
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1605428
Posted Wednesday, August 20, 2014 10:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 2,004, Visits: 5,480
Quick thought, this can easily be stuffed


;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
Post #1605654
Posted Thursday, August 21, 2014 8:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:15 AM
Points: 96, Visits: 179
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.
Post #1605849
Posted Thursday, August 21, 2014 9:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 19, 2014 4:23 AM
Points: 218, Visits: 442
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?
Post #1605894
Posted Thursday, August 21, 2014 9:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 20,703, Visits: 32,345
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.



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)
Post #1605906
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse