Join on two tables - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Join on two tables

Author

Topic

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2011-10-06 : 03:29:32

Hi,

I have two tables containing phone numbers, I want to join two tables but unable to get desired results, Please tell me how to do it right.

My query:
select a.Calledparty, b.Destinations, B.Tariff, B.Zone
FROM table_1 A, table_2 B
WHERE SUBSTRING(A.Calledparty,1, LEN(B.CODES)) = B.Codes

Sample Data:
Table_1
Calledparty
00447xxxxxx
00441xxxxxx
00446xxxxxx

Table_2
Codes
0044
00447
00441
0044643
00445643

I am getting wrong results like against 00447xxxxxxx it brings values of line 0044 from table_2 and sometime it comes up with correct results.

thank in advance
MIZ

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 03:33:05

what is the desired result ?

KH
[spoiler]Time is always against us[/spoiler]

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2011-10-06 : 04:13:30

I want to compare both tables and populate result where maximum number of digits from two columns match with each other.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 04:17:36

ok. So based on the sample data that you have provided how would the required / expected result looks like ?

KH
[spoiler]Time is always against us[/spoiler]

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2011-10-06 : 04:35:06

column 1 column 2
00447xxxxxx 00447
00441xxxxxx 00441
00446xxxxxx 0044643

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 05:18:21

how do you decide to match 00446xxxxxx with 0044643 ?

how about 0044 of Table 2. Why this is not match ?

Please explain your matching rules or criteria

KH
[spoiler]Time is always against us[/spoiler]

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2011-10-06 : 05:26:13

00446xxxxxx matches with 0044643 because there is no other value avaliable, i assume that 00446'xx'xxxx is 43 then it should matched with basic value, usually what happened in the result of query that it come with correct result but sometime the small value 0044 displayed against few values. which i dont want. I want exect matching against each number.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 06:05:42

try


select	*
from	Table_1 t1
	cross apply
	(
		select	top 1 Codes
		from	Table_2 t2
		where	t1.Calledparty	like t2.Codes + '%'
		order by len(Codes) desc
	) c

KH
[spoiler]Time is always against us[/spoiler]

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2011-10-06 : 08:20:21

its working ... thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources