Count of table rows from a joined table?

I have the following working store procedure:

SELECT M.*,MS.Status, C.Country, U.Title, U.Firstname, U.Surname FROM Meeting M 
	LEFT JOIN Country C ON C.ID = M.CountryID 
	LEFT JOIN [User] U on U.ID = M.UseriD 
	LEFT JOIN MeetingStatus MS on MS.ID = M.StatusID 
	(@MeetingID = -1 OR M.ID = @MeetingID) AND
	(@InitiativeID = -1 OR M.InitiativeID = @InitiativeID) AND
	(@UserID = -1 OR UserID = @UserID) AND 
	(@MDate = '' OR M.MeetingDate = @MDate) AND
	(@PostalCode = '' OR M.PostalCode = @PostalCode) AND 
	(@CountryID = -1 OR M.CountryID = @CountryID) AND
	(@Longitude = 0 OR M.Longitude = @longitude) AND
	(@Latitude = 0 OR M.Latitude = @Latitude) AND  
	(@Deleted = -1 OR M.Deleted = @Deleted)

Open in new window

Which pulls back a list of meetings  perfectly. The thing is; I need to add in the below SQL statement as a new column("AttendeeCount") in the above stored procedure, so we can see how many attendees there are for each meeting;

SELECT COUNT(MA.ID) FROM MeetingAttendee MA WHERE MA.MeetingID = @MeetingID

Open in new window

Obviously, when added in to the stored procedure "MA.MeetingID = M.ID"

How do I do this...
Who is Participating?
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
You can add the query directly to the SELECT portion:
SELECT M.*,MS.Status, C.Country, U.Title, U.Firstname, U.Surname,
  (SELECT COUNT(MA.ID) FROM MeetingAttendee MA WHERE MA.MeetingID = M.ID) AS MeetingCount

Open in new window

This is called a correlated query.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Since @MeetingID is a single value, try an uncorrrelated subquery
SELECT blah, blah, blah, (SELECT COUNT(ID) FROM MeetingAttendee WHERE MA.MeetingID = @MeetingID ) as aattendee_count
FROM blah
  LEFT JOIN blah blah blah

Open in new window

If it weren't for needing a single @MeetingID, a JOIN would have also worked to get all meetings and attendee counts.
SELECT blah, blah, blah, 
FROM blah
   JOIN (SELECT MeetingID, COUNT(ID) FROM MeetingAttendee GROUP BY MeetingID) ac ON m.MeetingID = ac.MeetingID

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.