Convert inner join to SQL Subquery

Question

Post reply

Convert inner join to SQL Subquery

maxyogesh2002

Ten Centuries

Points: 1062
More actions
July 15, 2009 at 6:54 am

#72389

Dear Friend,
my SQL Query is as follow:
i m using inner join in that query... i want to modify that into subquery
currently i m using SQL Server 2000
select distinct Auditdata.ID, ns.ProviderMaster_ID as CDRComment from Auditdata AuditData inner join AuditMaster am on am.ID = AuditData.AuditMaster_ID inner join HomeCircleMaster hcm on hcm.Ori_CircleMaster_ID = am.CircleMaster_ID and hcm.Ori_ServiceTypeMaster_ID = 1 and hcm.Dest_ServiceTypeMaster_ID = 1 inner join AuditTaggingMaster atm on atm.AuditMaster_ID = am.ID inner join NoSeriesMaster ns on ( ns.CircleMaster_ID = am.CircleMaster_ID or ns.CircleMaster_ID = hcm.Dest_CircleMaster_ID) and ns.ProviderMaster_ID <> am.ProviderMaster_ID and ns.ServiceTypeMaster_ID = 1 inner join ProviderMaster_CallTypeMaster pm_ctm on pm_ctm.ProviderMaster_ID = am.ProviderMaster_ID and pm_ctm.CallTypeMaster_ID = 101 and pm_ctm.CallTypeTagValue =AuditData.CallTypeTag INNER JOIN NoSeriesMaster_Prefix PD ON AuditData.CallTo like PD.PrefixNo + '%' AND AuditData.calltolen = PD.PrefixLen + PD.AfterPrefixLen AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, NoSeriesLen + PD.PrefixLen) where AuditData.TATCallType is null and AuditData.AuditMaster_ID = 74 AND PD.PrefixType = 'SMS
please help me
thanx

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's a lot of inner joins...which part do you weant to change into a subquery?

formatted for readability...the exisitng SQL made my eyes cross:

SELECT DISTINCT

Auditdata.ID,

ns.ProviderMaster_ID AS CDRComment

FROM Auditdata AuditData

INNER JOIN AuditMaster am

ON am.ID = AuditData.AuditMaster_ID

INNER JOIN HomeCircleMaster hcm

ON hcm.Ori_CircleMaster_ID = am.CircleMaster_ID

AND hcm.Ori_ServiceTypeMaster_ID = 1

AND hcm.Dest_ServiceTypeMaster_ID = 1

INNER JOIN AuditTaggingMaster atm

ON atm.AuditMaster_ID = am.ID

INNER JOIN NoSeriesMaster ns

ON ( ns.CircleMaster_ID = am.CircleMaster_ID

OR ns.CircleMaster_ID = hcm.Dest_CircleMaster_ID)

AND ns.ProviderMaster_ID am.ProviderMaster_ID

AND ns.ServiceTypeMaster_ID = 1

INNER JOIN ProviderMaster_CallTypeMaster pm_ctm

ON pm_ctm.ProviderMaster_ID = am.ProviderMaster_ID

AND pm_ctm.CallTypeMaster_ID = 101

AND pm_ctm.CallTypeTagValue =AuditData.CallTypeTag

INNER JOIN NoSeriesMaster_Prefix PD

ON AuditData.CallTo like PD.PrefixNo + '%'

AND AuditData.calltolen = PD.PrefixLen + PD.AfterPrefixLen

AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, NoSeriesLen + PD.PrefixLen)

WHERE AuditData.TATCallType is NULL

AND AuditData.AuditMaster_ID = 74

AND PD.PrefixType = 'SMS'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Lowell (7/15/2009)
there's a lot of inner joins...which part do you weant to change into a subquery?

and why?

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