Databases
StackOverflow2013
Postsdbo
POHow to link sql sub queries together?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to link sql sub queries together?
    text
    copied!<p>is it possible to link the following sub query (see below.) The first three sub queries work fine however I'm struggling to see how to do the rest any guidance would be great cheers. </p> <p>Ps. apologies for the long code</p> <pre><code>SELECT c.[Status], c.CompanyId, c.Name, (SELECT count(DISTINCT usr.UserID) FROM [ondemand.10cms.com].Security.[user] usr INNER JOIN [ondemand.10cms.com].Company.Company ON usr.CompanyID = Company.CompanyID WHERE usr.CompanyID = c.CompanyID) AS TotalUsers, (SELECT sum (CASE WHEN usr.Status = 2 THEN 1 ELSE 0 END) FROM [ondemand.10cms.com].Security.[user] usr INNER JOIN [ondemand.10cms.com].Company.Company ON usr.CompanyID = Company.CompanyID WHERE usr.CompanyID = c.CompanyID) AS ActiveUsers, (SELECT sum (CASE WHEN usr.Status = 3 THEN 1 ELSE 0 END) FROM [ondemand.10cms.com].Security.[User] usr INNER JOIN [ondemand.10cms.com].Company.Company ON usr.CompanyID = Company.CompanyID WHERE usr.CompanyID = c.CompanyID) AS SuspendedUsers, (Select COUNT (distinct usrs.id) From [ondemand.10cms.com].Security.UserSession usrs inner join [ondemand.10cms.com].Security.[user] usr on usrs.UserID=usr.UserID ) as TotalLogin, (Select COUNT( MerchandisingModule.Name) From [ondemand.10cms.com].Project.Template inner join [ondemand.10cms.com].Project.MerchandisingModule on Template.TemplateID= MerchandisingModule.TemplateId )as CurrentModules, (Select count(MerchandisingModule.CreatedDate) from [ondemand.10cms.com].Project.MerchandisingModule inner join [ondemand.10cms.com].Project.Template on Template.TemplateID= MerchandisingModule.TemplateId )as ModulesCreated, (Select count(mm.UpdatedDate) from [ondemand.10cms.com].Project.MerchandisingModule mm inner join [ondemand.10cms.com].Project.Template on Template.TemplateID= mm.TemplateId )as ModulesUpdated, (Select COUNT(MA.MerchandisingAreaID) from [ondemand.10cms.com].Project.MerchandisingArea MA inner join [ondemand.10cms.com].Project.Project on Project.ProjectID= MA.ProjectID ) as Currentareas, (Select COUNT (MA.name) from [ondemand.10cms.com].Project.MerchandisingArea MA inner join [ondemand.10cms.com].Project.Project on Project.ProjectID= MA.ProjectID ) as AreasCreated, (select COUNT (MerchandisingArea.UpdatedDate) from [ondemand.10cms.com].Project.MerchandisingArea inner join [ondemand.10cms.com].Project.Project on Project.ProjectID= MerchandisingArea.ProjectID ) as AreasUpdated, (Select SUM ( case when MA.PublishStatus = 1 then 1 else 0 end) from [ondemand.10cms.com].Project.MerchandisingArea MA inner join [ondemand.10cms.com].Project.PublishingStatus on PublishingStatus.PublishStatusId = MA.PublishStatus ) as SuccessPublished, (Select SUM ( case when MA.PublishStatus = 3 then 1 else 0 end) from [ondemand.10cms.com].Project.MerchandisingArea MA inner join [ondemand.10cms.com].Project.PublishingStatus on PublishingStatus.PublishStatusId= MA.PublishStatus ) as FailedPublished from [ondemand.10cms.com].Company.Company c </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload