Please suggest for count with inner query

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)

Please suggest for count with inner query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-10-30 : 07:54:23

Select dbo.[User].FirstName
, dbo.[User].LastName
, dbo.[User].Email
, dbo.FormInstance.FormInstanceId
, dbo.FormInstance.IsLocked
, dbo.FormInstance.InsertedDate
, dbo.FormInstance.UpdatedDate
, dbo.Form.FormName
, ts.SectionOrder
, (Select Count(*)
From dbo.FormSection tsstotal
Where tsstotal.FkFormId = dbo.Form.FormId) TotalSections
From dbo.[User]
Inner Join dbo.FormInstance
On dbo.[User].UserId = dbo.FormInstance.FkUserId
Inner Join dbo.InstanceLastVistiedSectionInfo
On dbo.FormInstance.FormInstanceId = dbo.InstanceLastVistiedSectionInfo.FkFormInstanceId
Inner Join dbo.Form
On dbo.FormInstance.FkFormId = dbo.Form.FormId
Inner Join dbo.FormSection ts
On dbo.InstanceLastVistiedSectionInfo.FkFormSectionId = ts.FormSectionId
And dbo.Form.FormId = ts.FkFormId
Where dbo.FormInstance.FormInstanceId = @FormInstanceId

What I wants is remove
(Select Count(*)
From dbo.FormSection tsstotal
Where tsstotal.FkFormId = dbo.Form.FormId)
With some sort of join but the dbo.FormSection is already there in for some other prespective

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 08:14:32

you dont need any extra join. just tweak derived table as below and see


Select dbo.[User].FirstName
, dbo.[User].LastName
, dbo.[User].Email
, dbo.FormInstance.FormInstanceId
, dbo.FormInstance.IsLocked
, dbo.FormInstance.InsertedDate
, dbo.FormInstance.UpdatedDate
, dbo.Form.FormName
, ts.SectionOrder
, ts.Cnt AS TotalSections
From dbo.[User]
Inner Join dbo.FormInstance
On dbo.[User].UserId = dbo.FormInstance.FkUserId
Inner Join dbo.InstanceLastVistiedSectionInfo
On dbo.FormInstance.FormInstanceId = dbo.InstanceLastVistiedSectionInfo.FkFormInstanceId
Inner Join dbo.Form
On dbo.FormInstance.FkFormId = dbo.Form.FormId
Inner Join (SELECT *, COUNT(1) OVER (PARTITION BY FkFormId) AS Cnt FROM  dbo.FormSection) ts
On dbo.InstanceLastVistiedSectionInfo.FkFormSectionId = ts.FormSectionId
And dbo.Form.FormId = ts.FkFormId
Where dbo.FormInstance.FormInstanceId = @FormInstanceId

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-10-30 : 08:31:08

thanks visakh16

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 08:44:13

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources