Reputation: 2760

Problem with joining to select statements

SELECT COUNT( WiningComment) AS 'WinningAnswers'
FROM Threads
WHERE WiningComment IN (SELECT CommentsID
FROM Comments
WHERE  UsersID=@UserID)

UNION ALL
SELECT COUNT(CommentsID)  AS 'TotalAnswers'
FROM  Comments
WHERE  UsersID=@UserID

I want TotalAnswers and WinningAnswers to appear as two seperate columns. But instead I get one row with two figures when I test the query...

Upvotes: 0

Answers (5)

Bruno Gautier

Reputation: 656

You can for example do a little bit of grouping:

SELECT MAX(WinningAnswers) AS 'WinningAnswers', MAX(TotalAnswers) AS 'TotalAnswers'
FROM (
    SELECT t.UsersID, COUNT(t.WiningComment) AS 'WinningAnswers', 0 AS 'TotalAnswers'
    FROM Threads t
      JOIN Comments c ON c.CommentsID = t.WiningComment
    WHERE t.UsersID = @UserID
    UNION ALL
    SELECT UsersID, 0 AS 'WinningAnswers', COUNT(CommentsID) AS 'TotalAnswers'
    FROM Comments
    WHERE UsersID = @UserID
  ) AS Total
GROUP BY UsersID;

or, considering that your second query just returns one record, you can simply:

SELECT COUNT(t.WiningComment) AS 'WinningAnswers', Total.TotalAnswers AS 'TotalAnswers'
FROM (
      SELECT COUNT(CommentsID) AS 'TotalAnswers'
      FROM Comments
      WHERE UsersID = @UserID
    ) AS Total, Threads t
  JOIN Comments c ON c.CommentsID = t.WiningComment
WHERE t.UsersID = @UserID;

Upvotes: 0

Petar Ivanov

Reputation: 93030

SELECT (
    SELECT COUNT( WiningComment)
    FROM Threads
    WHERE WiningComment IN (SELECT CommentsID
    FROM Comments
    WHERE  UsersID=@UserID)
) as 'WinningAnswers',
(
    SELECT COUNT(CommentsID)
    FROM  Comments
    WHERE  UsersID=@UserID
) as 'TotalAnswers'

Upvotes: 1

marc_s

Reputation: 754488

The UNION operator by definition joins two result sets and combines their rows - two result set = at least two rows.

What you are looking for is something like a single SELECT with two sub-selects to get those values:

SELECT 
    (SELECT COUNT(WiningComment) 
     FROM dbo.Threads t
     INNER JOIN dbo.Comments c ON t.WiningComment = c.CommentsID
     WHERE UsersID = @UserID) AS 'WinningAnswers',

   (SELECT COUNT(CommentsID) 
    FROM  dbo.Comments
    WHERE  UsersID = @UserID) AS 'TotalAnswers'

Upvotes: 2