how to join two tables having no common row and no primary key in any of the two table

Question

Post reply

how to join two tables having no common row and no primary key in any of the two table

jitendrapatil2006

SSC Journeyman

Points: 80
More actions
April 14, 2008 at 1:15 am

#184425

Hi friends i am new to this forum and i using sql server 2005 express edition i am haivng two tables 1st one having coloum names as "
starting_ date, ani,diled_digits,rounded_dur,actual_dur,cost"
and 2nd having coloumn name as " countryname,code,rate/min" so i want to query this table . i want countryname and code from 2nd table and starting_date, rounded_dur, cost from 1st table but the problem is i don't have any primary or foreing key defined in any of the table so how to do this please help me..... you can send me the reply on my email id
jitendrapatil2006@hotmail.com"> jitendrapatil2006@hotmail.com

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How are the two tables related to each other? How do you tell which starting date goes with which country?

Please take a look at the following article for suggestions on how to post your problems for fast and accurate answers

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jitendrapatil2006 SSC Journeyman Points: 80 More actions · Answer 2

sorry for the post as i am new to this forum therefore i made this mistake. I am reading a CSV file which contains the whole data, on reading the file i am storing the whole data in table now the 1st table i have created is like this:

start_date_time varchar(50) allownull =true

ani varchar(50) allownull = true

dialed_digits varchar(50) allownull=true

actual_dur varchar(50) allownull=true

rounded_dur varchar(50) allownull=true

cost varchar(50) allownull=true

and the second table is like this

country varchar(50) allownull=true

code varchar(50) allownull=true

rate/min varchar(50) allownull=true

i have to create this kind of table because the file contains data in such manner now i want to join this table so that i can get the resultant view with start_date_time, dialed_digits, rounded_dur, cost,country,code,rate/min fields now how to achieve this....

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

How are the two tables related to each other? How do you tell which starting date goes with which country?

Can you give a few rows of sample data and what you want as results please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jitendrapatil2006 SSC Journeyman Points: 80 More actions · Answer 4

see the data for the first table is like this:

start_date_time ani dialed_digits actual_dur rounded_dur

03/01/2008 00:05:57 629172162448 923455755684 2 2

03/01/2008 00:15:56 79279906564 79278454880 51 60

03/01/2008 00:16:51 4166143724 92922202502 188 188

03/01/2008 00:23:13 07956563557 925871021085 1020 1020

03/01/2008 00:38:13 639262060046 923084230440 1 1

03/01/2008 00:47:02 -------------- 92945623075 124 124

cost

0.002800

0.135000

0.256900

1.393400

0.001400

0.169400

and the second tables data is like this:

Country Code Rate/Min

PAKISTAN 92 0.0228

PAKISTAN ISLAMABAD 9251 0.0228

PAKISTAN KARACHI 9221 0.0228

PAKISTAN LAHORE 9242 0.0228

PAKISTAN MOBILE 923 0.0228

PAKISTAN MOBILE 92300 0.0228

and now i want to create a view which can have start_date_time , dialed_digits , rounded_durations , cost , country , code , Rate /Min coloumns . so can you help me plz..

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

jitendrapatil2006 (4/14/2008)
sorry for the post as i am new to this forum therefore i made this mistake. I am reading a CSV file which contains the whole data, on reading the file i am storing the whole data in table now the 1st table i have created is like this:
start_date_time varchar(50) allownull =true
ani varchar(50) allownull = true
dialed_digits varchar(50) allownull=true
actual_dur varchar(50) allownull=true
rounded_dur varchar(50) allownull=true
cost varchar(50) allownull=true
and the second table is like this
country varchar(50) allownull=true
code varchar(50) allownull=true
rate/min varchar(50) allownull=true
i have to create this kind of table because the file contains data in such manner now i want to join this table so that i can get the resultant view with start_date_time, dialed_digits, rounded_dur, cost,country,code,rate/min fields now how to achieve this....

Not so obvious to the casual user, this is telephony rating and you will have to match the "Code" for the country to the digits in the either the ANI or, more likely, the Dialed_Digits. The problem is that a Country "Code" can be 1 to 3 digits... and the join you do must do the same...

The problem with that should be a bit obvious and you can't really do just a join to do this. You must, instead, find all the 3 digit codes and mark the CDRs (Call Detail Records) that match the 3 digit Country Codes. Then do the same thing with all 2 digit country codes that don't have 3 digit matches. Then do the same thing with all 1 digit country codes that don't have 2 or 3 digit matches... but ONLY if an international number was dialed!

Since Country Codes are unique, you could (maybe) write a view to do a join (although updating a table as above will always be faster in the long run of SELECTs)... The WHERE clause might look like this for INTERNATIONAL DIALED calls, only...

WHERE Code = SUBSTRING(Dialed_Digits,1,3)

OR Code = SUBSTRING(Dialed_Digits,1,2)

OR Code = SUBSTRING(Dialed_Digits,1,1)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RyanRandall SSChampion Points: 13623 More actions · Answer 6

How's this?

--Preparation

declare @t1 table (start_date_time datetime, ani varchar(20), dialed_digits varchar(20), actual_dur int, rounded_dur int, cost money)

insert @t1

select '03/01/2008 00:05:57', '629172162448', '923455755684', 2, 2, 0.002800

union all select '03/01/2008 00:15:56', '79279906564', '79278454880', 51, 60, 0.135000

union all select '03/01/2008 00:16:51', '4166143724', '92922202502', 188, 188, 0.256900

union all select '03/01/2008 00:23:13', '07956563557', '925871021085', 1020, 1020, 1.393400

union all select '03/01/2008 00:38:13', '639262060046', '923084230440', 1, 1, 0.001400

union all select '03/01/2008 00:47:02', '--------------', '92945623075', 124, 124, 0.169400

declare @t2 table (Country varchar(30), Code varchar(10), [Rate/Min] money)

insert @t2

select 'PAKISTAN', '92', 0.0228

union all select 'PAKISTAN ISLAMABAD', '9251', 0.0228

union all select 'PAKISTAN KARACHI', '9221', 0.0228

union all select 'PAKISTAN LAHORE', '9242', 0.0228

union all select 'PAKISTAN MOBILE', '923', 0.0228

union all select 'PAKISTAN MOBILE', '92300', 0.0228

--Calculation

; with b as (select a.dialed_digits, max(len(Code)) as LongestCodeLength

from @t1 a inner join @t2 b on a.dialed_digits like b.Code + '%' group by a.dialed_digits)

select a.start_date_time, a.dialed_digits, a.rounded_dur, a.cost, c.country, c.code , c.[Rate/Min]

from @t1 a left outer join b on a.dialed_digits = b.dialed_digits

left outer join @t2 c on a.dialed_digits like c.Code + '%' and b.LongestCodeLength = len(c.Code)

/* Results

start_date_time dialed_digits rounded_dur cost country code Rate/Min

----------------------- -------------------- ----------- --------------------- ------------------------------ ---------- ---------------------

2008-03-01 00:05:57.000 923455755684 2 0.0028 PAKISTAN MOBILE 923 0.0228

2008-03-01 00:15:56.000 79278454880 60 0.135 NULL NULL NULL

2008-03-01 00:16:51.000 92922202502 188 0.2569 PAKISTAN 92 0.0228

2008-03-01 00:23:13.000 925871021085 1020 1.3934 PAKISTAN 92 0.0228

2008-03-01 00:38:13.000 923084230440 1 0.0014 PAKISTAN MOBILE 923 0.0228

2008-03-01 00:47:02.000 92945623075 124 0.1694 PAKISTAN 92 0.0228

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

jitendrapatil2006 (4/14/2008)
and now i want to create a view which can have start_date_time , dialed_digits , rounded_durations , cost , country , code , Rate /Min coloumns . so can you help me plz..

Probably. But, as I said above, I still need to know how those two tables relate to each other.

If I look at the first row from table 1 (03/01/2008 00:05:57 629172162448 923455755684), what country is supposed to appear with that? Why?

As I asked above, can you give the desired output. ie, for the rows that you gave above, what would the output of that view be?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

Gail,

The country "Code" has to match the first 1 to 3 digits of either the ANI or the Dial_Digits. You wouldn't know that unless you've worked with Telephony Rating Systems before...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

Also, there's a City code that can get involved depending on the level of detail you want to show. For interational calls, the first 1-3 digits can be the country code and the second 0 to 4 digits can be the city code. It's not quite as bad as it sounds because the country codes are pretty much unique although the city code can make a difference.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Jeff Moden (4/14/2008)
Gail,
The country "Code" has to match the first 1 to 3 digits of either the ANI or the Dial_Digits. You wouldn't know that unless you've worked with Telephony Rating Systems before...

I guessed it was something like that. I wasn't sure and didn't feel like writing code based on a guess (code which would probably have to be rewritten)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jitendrapatil2006 SSC Journeyman Points: 80 More actions · Answer 11

hi jeff thanks for your help i have tried your query to create a view and i have tried it like this :

SELECT dbo.table1.country, dbo.table1.code, dbo.table.rate_min, dbo.table2.start_date_time, dbo.table2.rounded_dur, dbo.table2.cost,

dbo.table2.dialed_digits

FROM dbo.table1 INNER JOIN

dbo.table2 ON dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 2) OR

dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 3) OR

dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 4) OR

dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 5) OR

dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 6)

but the problem here is it is giving me wrong no of records , the original table1 contains country,code, Rate/min with 286 records and table2 contains start_date_time,ani,actual_dur,rounded_dur,dialed_digits,cost

with 532163 records so what should i do to get the desired output please help me...

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

Please show me, say, the first 20 CDRs from the table and the related rows from the country table. I recommend that you do this as outlined in the URL in my signature... it will save me a huge amount of time without costing you much time and will allow me to spend more time on your problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jitendrapatil2006 SSC Journeyman Points: 80 More actions · Answer 13

ok see this is the data of cdr file ok

--===== If the test table already exists, drop it

IF OBJECT_ID('Temp DB ,table1','U') IS NOT NULL

DROP TABLE table1

--===== Create the test table with

CREATE TABLE table 1

(

start_date_time varchar(50),

ani varchar(50),

actual_dur varchar(50),

rounded_dur varchar(50),

dialed_digits varchar(50)

cost varchar(50)

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT table1 ON

--===== Insert the test data into the test table

INSERT INTO table1

(start_date_time, ani, actual_dur, rounded_dur, dialed_digits, cost)

SELECT '03/01/2008 00:05:57' ,'629172162448' ,'923455755684' ,'2' ,'2' ,'0.002800' UNION ALL

SELECT '03/01/2008 00:15:56' ,'79279906564' ,'79278454880' '51' '60' ,'0.135000' UNION ALL

SELECT '03/01/2008 00:16:51 ',4166143724' ,'92922202502' ,'188' ,'188' ,0.256900 UNION ALL

SELECT '03/01/2008 00:23:13' ,'07956563557' ,'925871021085' ,'1020' ,'1020' ,'1.393400' UNION ALL

SELECT '03/01/2008 00:38:13' ,'639262060046' ,'923084230440' ,'1' ,'1' ,'0.001400' UNION ALL

--===== Set the identity insert back to normal

SET IDENTITY_INSERT table1 OFF

and this is the data for the country file

CREATE TABLE table 2

(

country varchar(50),

code varchar(50),

rate_min varchar(50),

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT table2 ON

--===== Insert the test data into the test table

INSERT INTO table2

(country, code, rate_min)

SELECT 'PAKISTAN','92','0.0228' UNION ALL

SELECT 'PAKISTAN ISLAMABAD','9251','0.0228' UNION ALL

SELECT 'PAKISTAN KARACHI','9221','0.0228' UNION ALL

SELECT 'PAKISTAN LAHORE','9242','0.0228' UNION ALL

SELECT 'PAKISTAN MOBILE','923','0.0228' UNION ALL

--===== Set the identity insert back to normal

SET IDENTITY_INSERT table2 OFF

see you can try it out with this and if not sufficient i can send my both of the csv file to you give me your email address i will sent the file having maximum records ok.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 14

Heh... nice try... obviously, you didn't try the code you sent me... there are over 12 errors, the columns in Table2 are out of order with the data, and there are no matches in Table1 for anything in Table2.

If you want help with this, slow down and do it right, please. I don't mind helping folks, but if you don't have the time to help me help you, then guess how much time I'm gonna spend on the problem... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)