Telephone referencing - 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 2005 Forums

Transact-SQL (2005)

Telephone referencing

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-12 : 19:18:35

Hi

I have two tables. The first has basic details for clients such as Name, DOB, Phone, Mobile. The second has a list of calls made which has the number of the call. This table has 4000 records

I am trying to link the two tables where the number in the second table references either the Phone or Mobile Column in the first table.

I am using the following query but only get 2500 records instead of 4000. Does anyone know what can be done?

SELECT
b.username, b.forename, b.surname,cl.LogID, cl.Extension, cl.CallTime, cl.CallType, cl.CallNumber, cl.CallCost,
cl.CallDuration, cl.EXT2, cl.DDI, cl.CreatedBy, cl.CreatedOn, cl.UpdatedBy,
cl.UpdatedOn
FROM callLog cl
INNER JOIN tblBod b ON cl.CallNumber = b.Phone
where exists (select phone, mobile from tblbod)

UNION ALL

SELECT
b1.username, b1.forename, b1.surname,cl1.LogID, cl1.Extension, cl1.CallTime, cl1.CallType, cl1.CallNumber,
cl1.CallCost, cl1.CallDuration, cl1.EXT2, cl1.DDI, cl1.CreatedBy,
cl1.CreatedOn, cl1.UpdatedBy, cl1.UpdatedOn

FROM callLog AS cl1

INNER JOIN tblBod AS b1 ON cl1.CallNumber = b1.Mobile
where exists (select phone, mobile from tblbod)

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-12 : 19:26:50

Have you run each query individually to see which one is the culprit?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 20:03:29

[code]SELECT b.username,
b.forename,
b.surname,
cl.LogID,
cl.Extension,
cl.CallTime,
cl.CallType,
cl.CallNumber,
cl.CallCost,
cl.CallDuration,
cl.EXT2,
cl.DDI,
cl.CreatedBy,
cl.CreatedOn,
cl.UpdatedBy,
cl.UpdatedOn
FROM tblBod as b
INNER JOIN callLog as cl ON cl.CallNumber IN (b.Phone, b.Mobile)[/code]

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-13 : 03:47:20

Hi

I tried running the query and it returned 2706 rows.
I ran the following statement to check if the numbers exist in the first table, and it gave me the full result of 4000 records

select callnumber from CallLogs
where exists (Select phone, mobile from tblbod)

Any suggestions?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 04:05:13

use the correct syntax.

select callnumber from CallLogs
where exists (Select * from tblbod where tblbod.phone = CallLogs.callnumber or tblbod.mobile = CallLogs.callnumber)

what's wrong with my suggestion?

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-13 : 04:16:31

Hi

I tried running each part individually, the first section gave me 2570, the second gave me 188.

I have a feeling that it may not be picking up the same number twice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources