Sql Statment ProbLem
-
Hi to all select ModelName from SRO_ModelMaster select RegionCode from cmp_RegionMaster SELECT Count(*) as 'TOT' FROM SRO_SurveySelectedModel,cmp_RegionMaster WHERE modelno in (Select ModelNo From SRO_ModelMaster where ModelName='FNRO') AND approved='Y' AND RosapCode in ( SELECT ROsapCode FROM SRO_ROMaster where region='ER') Please tell me how i can combine these three statments into Single SQL Statment I want to develop Stored procedure From this without using temp table Thanks & regard Sonia
-
Hi to all select ModelName from SRO_ModelMaster select RegionCode from cmp_RegionMaster SELECT Count(*) as 'TOT' FROM SRO_SurveySelectedModel,cmp_RegionMaster WHERE modelno in (Select ModelNo From SRO_ModelMaster where ModelName='FNRO') AND approved='Y' AND RosapCode in ( SELECT ROsapCode FROM SRO_ROMaster where region='ER') Please tell me how i can combine these three statments into Single SQL Statment I want to develop Stored procedure From this without using temp table Thanks & regard Sonia
Basically you need to join those tables and then group by ModelName and RegionCode but to get this statement further: what is the relationship between those four tables? Your query should look something like:
SELECT SRO_ModelMaster.ModelName,
cmp_RegionMaster.RegionCode,
Count(*) as 'TOT'
FROM SRO_SurveySelectedModel,
cmp_RegionMaster,
SRO_ModelMaster,
SRO_ROMaster
WHERE ???.modelno = SRO_ModelMaster.ModelNo
AND SRO_ModelMaster.ModelName='FNRO'
AND ???.approved='Y'
AND ???.RosapCode = SRO_ROMaster.ROsapCode
AND SRO_ROMaster.region='ER'
AND SRO_ROMaster.??? = ???.???
GROUP BY SRO_ModelMaster.ModelName,
cmp_RegionMaster.RegionCodeThe need to optimize rises from a bad design. My articles[^]