Performing a count on an additional table.

[mike55]

I have 5 tables thare are linked using a primary key, these are: Organization, Org_Membership, SMS_Credit, Login, and Org_Profile. There is also a sixth table: SentMessages. Currently I am running a command that select some data from each of the tables, places the data in a dataset and sends that to be binded to a gridview on my .aspx page.

Here is the sql command that I am using:

SELECT     dbo.Organization.Org_ID, dbo.Organization.Org_Name, dbo.SMS_Credit.Credit, CONVERT(VARCHAR(10), dbo.Org_MemberShip.Start_Date, 103) 
                      AS Start_Date, CONVERT(VARCHAR(10), dbo.Org_MemberShip.Finish_Date, 103) AS Finish_Date, dbo.Org_MemberShip.Status, 
                      dbo.Org_MemberShip.Subscription_Type, dbo.Organization.Org_Country, dbo.Organization.DialingCode, dbo.Organization.Org_Email, 
                      dbo.Organization.Org_Phone, dbo.Organization.Addr1, dbo.Organization.Town, dbo.Organization.County, dbo.Organization.Member_Numb, 
                      dbo.Login.Email_Add, dbo.Login.UserName, dbo.Login.PlainPass, dbo.Org_Profile.AllowContact, dbo.Login.Login, dbo.Login.Forename, 
                      dbo.Login.Surname
FROM         dbo.Organization INNER JOIN
                      dbo.Org_MemberShip ON dbo.Organization.Org_ID = dbo.Org_MemberShip.Org_ID INNER JOIN
                      dbo.SMS_Credit ON dbo.Organization.Org_ID = dbo.SMS_Credit.Org_ID INNER JOIN
                      dbo.Login ON dbo.Organization.Org_ID = dbo.Login.Org_ID INNER JOIN
                      dbo.Org_Profile ON dbo.Organization.Org_ID = dbo.Org_Profile.Org_ID 
where (Org_Membership.Subscription_Type = trial or Org_Membership.Subscription_Type = full)

The above command runs correctly and returns all the data that I need. I have now been asked to calculate and display on the gridview the number of messages that each organisation has sent. This information is stored in the SentMessages table, which has all the messages sent and the organisation that sent it. To extract the information from this table, all I need to do is to use the following command:

Select org_ID, count(org_id) as exp1
From SentMessages
Group by org_id

The problem is how can I link both commands together and have the second command performed based on the org_ID selected in the first command? One option that I have is to take the results of the first command and loop through it selecting the org_id and going to the database. However this would result in a significant performance overhead.

Mike55.

 

[tfowler]

Ive done something similar to this in the past. I think it would be something like:

SELECT     dbo.Organization.Org_ID, dbo.Organization.Org_Name, dbo.SMS_Credit.Credit, CONVERT(VARCHAR(10), dbo.Org_MemberShip.Start_Date, 103)
                      AS Start_Date, CONVERT(VARCHAR(10), dbo.Org_MemberShip.Finish_Date, 103) AS Finish_Date, dbo.Org_MemberShip.Status, 
                      dbo.Org_MemberShip.Subscription_Type, dbo.Organization.Org_Country, dbo.Organization.DialingCode, dbo.Organization.Org_Email, 
                      dbo.Organization.Org_Phone, dbo.Organization.Addr1, dbo.Organization.Town, dbo.Organization.County, dbo.Organization.Member_Numb, 
                      dbo.Login.Email_Add, dbo.Login.UserName, dbo.Login.PlainPass, dbo.Org_Profile.AllowContact, dbo.Login.Login, dbo.Login.Forename, 
                      dbo.Login.Surname, message_count=
                          (SELECT COUNT(dbo.SentMessages.org_id) FROM dbo.SentMessages
                          WHERE dbo.SentMessages.org_id=dbo.Organization.Org_ID)
FROM         dbo.Organization INNER JOIN
                      dbo.Org_MemberShip ON dbo.Organization.Org_ID = dbo.Org_MemberShip.Org_ID INNER JOIN
                      dbo.SMS_Credit ON dbo.Organization.Org_ID = dbo.SMS_Credit.Org_ID INNER JOIN
                      dbo.Login ON dbo.Organization.Org_ID = dbo.Login.Org_ID INNER JOIN
                      dbo.Org_Profile ON dbo.Organization.Org_ID = dbo.Org_Profile.Org_ID 
where (Org_Membership.Subscription_Type = trial or Org_Membership.Subscription_Type = full)

Todd

 

[mike55]

Thanks tfowler

Your suggestion works correctly, I have had to do some modifying of the query.

Mike55.