I am trying to select a count from another table in my query and I cant stop it from duplicating a row when I include the sub selection. I'm guessing it's because its finding two records in the table it's counting from but I'm expecting just one record to be returned - the count of these records, as there is only one record in the outer selection:
SELECT c.Name, e.EventID, e.DateTime, e.AdditionalComments,
(SELECT COUNT(*) FROM AlertedEvents WHERE EventID = e.EventID) AS 'RequestedJoin'
FROM Events e LEFT JOIN Clubs c ON e.ClubID = c.ClubId
LEFT JOIN AlertedEvents a ON e.EventID = a.EventID
WHERE e.UserID = 'blah blah'
AND e.DateTime > GETDATE()
AND e.Archived = 'False'
All I want is one field counting the records in AlertedEvents where the EventID is matched (should be 2), instead of replicating a row in the overall query as the outer select query only returns one row. Any help appreciated!