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 appe开发者_如何学Goar as two seperate columns. But instead I get one row with two figures when I test the query...

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'

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'

That's what a UNION does... it creates a union of two data sets.

(1,2,3) UNION (4,5,6) = (1,2,3,4,5,6)

You essentially have two separate queries, so you should probably just do two separate queries.

This cannot be done, if you are using union, column names must be same in both queries. Try to make single query.

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;