SQL Joins

Question

SQL Joins

balu.arunkumar

Ten Centuries

Points: 1132
More actions
February 11, 2014 at 9:22 am

#304307

Hi,
I have a two table. One Table having Country code and country name details. Another table having
Customer mobile nos and Its around 24 crore records.When i use my query duplicate records created. Can i have the correct query?
Sample Records:
Table1:
1323XXXXXX
1240XXXXXX
1215XXXXXX
1682XXXXXX
1403XXXXXX
Table2:
CodeCountry
1USA
1204Canada
1242Bahamas
1246Barbados
1250Canada
1264Anguilla
1268AntiguaandBarbuda
1284VirginIslands(UK)
1289Canada
1306Canada
1340VirginIslands(US)
1345CaymanIslands
1403Canada
1416Canada
1418Canada
Query I'm using:
select a.MobileNo,CCD.Country
into tempCountry
from CustomerMobileno(nolock) a Left join Country_Code_Name CCD(nolock)
on CCD.code=left(a.Dialed_number,len(CCD.code))

Viewing 4 posts - 1 through 3 (of 3 total)

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Please read the first article in my signature and following the recommend guidelines listed in there about posting these types of questions to the forum. After you have supplied the DDL, DML and expected output someone should be able to help. Thanks

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

you always will have trouble with this join as your US code of '1' can match any fialed number starting with 1.

BTW: don't use NOLOCK - it's a pass to troubles...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

And be careful with those NOLOCK hints. It seems this may be ok in this situation but are you aware of what that hint brings to the table? Missing and/or duplicate data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/