Solved

SQL Sub-Select

Posted on 2013-05-28
2
528 Views
Last Modified: 2013-05-28
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'

Open in new window


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!
0
Comment
Question by:Alex_W
2 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
Comment Utility
Doesn't look like you are using the AlertedEvents in the results other than the sum so why not just remove the LEFT JOIN to it?


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
   WHERE e.UserID = 'blah blah'
  AND e.DateTime > GETDATE()
  AND e.Archived = 'False'
0
 
LVL 7

Author Closing Comment

by:Alex_W
Comment Utility
Joining to that table and including the count must have produced the replicated row.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now