SQL-How to Join two tables without similar columns

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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL-How to Join two tables without similar columns

Author

Topic

russel73
Starting Member

3 Posts

Posted - 2010-09-22 : 00:32:46

Guys/Gals
I have two tables, first one is 'Calls' which is having a list of telephone calls captured from a pbx and the second one is having the 'tariff' for international destinations. Pls find some sample data below :-
Calls

cDate	cTime	Party1	Party2	Party3	cDuration	DigitsDialed
7/1/2010	12:13:00 PM	7209	372		00:00:10	0027119024480
7/1/2010	12:10:00 PM	7209	373		00:00:05	0027119024480
7/1/2010	12:14:00 PM	7209	373		00:00:38	0027825601776
7/1/2010	10:05:00 AM	7209	375		00:36:26	0064212310005
7/1/2010	10:03:00 AM	7209	372		00:00:05	006421231005
7/1/2010	11:04:00 AM	7209	376		01:00:10	006448025697
7/5/2010	12:04:00 PM	7209	372		00:26:35	0027119024480
7/7/2010	5:54:00 PM	7209	373		00:14:42	0027119024480
7/15/2010	4:40:00 PM	7209	373		00:09:56	00441464851166

Tariff

Country	IDDCode	Normal	OffPeak
USA / Canada	001	$2.12	$1.37
Bahamas	001242	$5.63	$5.00
Guam	001671	$4.09	$3.27
Egypt	0020	$2.40	$1.89
Morocco	00212	$2.69	$1.91
Algeria	00213	$2.40	$1.89
Tunisia	00216	$2.40	$1.89
Libya	00218	$3.21	$2.81
Gambia	00220	$2.95	$2.43
Senegal	00221	$2.69	$1.91
Mauritania	00222	$2.40	$1.89
Mali	00223	$3.53	$3.00

What I would like to do is to link calls.digitsdialed (the first 4/5 chars) and tariff.iddcode so that I can extract the call charges for each call from the tariff table; now the trouble is the IDD code length is not constant so I'm searching for a way to like these columns together. Appreciate your help in advance.

Russel

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-22 : 06:21:40

This is kinda nasty and will blow your performance but it should do what you want. Hopefully there is a better way...:

SELECT DigitsDialed, b.*
FROM calls a
  INNER JOIN tariff b
    ON LEFT(DigitsDialed, LEN(b.IDDCODE)) = b.IDDCODE

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-22 : 06:35:42

You could also try:

SELECT DigitsDialed, b.*
FROM calls a
  INNER JOIN tariff b
    ON a.DigitsDialed LIKE b.IDDCODE +'%'

russel73
Starting Member

3 Posts

Posted - 2010-09-22 : 10:30:08

quote:
Originally posted by Lumbago

This is kinda nasty and will blow your performance but it should do what you want. Hopefully there is a better way...:
SELECT DigitsDialed, b.*
FROM calls a
INNER JOIN tariff b
ON LEFT(DigitsDialed, LEN(b.IDDCODE)) = b.IDDCODE

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Thanks man, I'll check this out tomorrow and will let you know the results. You are right about the performance since we are talking about 200K calls every day :o)
I think I'll have to implement this in code in the call logger itself.

russel73
Starting Member

3 Posts

Posted - 2010-09-22 : 10:35:20

quote:
Originally posted by robvolk

You could also try:
SELECT DigitsDialed, b.*
FROM calls a
INNER JOIN tariff b
ON a.DigitsDialed LIKE b.IDDCODE +'%'

Thanks robvolk for the statement, but I've tried this already. The trouble is in some cases there is more than one country with similar IDD code, for eg. USA is 001 but Bahamas is 001242 and your query will return both results.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-23 : 06:50:01

And as a general remark you'd probably be far better off storing you cDate and cTime columns as one single cDatetime and use the datetime data type instead. Storing cDuration as an int in number of seconds would probably also make things easier for you (I used to work for a phone company).

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources