SQL query that looks within the same table

Question

Apologies - am not good at SQL. Perhaps this is a simple query - I don't know.

What I have is a basic forum table - e.g.

id poster title type content postedon parentID

in this scheme, if a user asks a question, it is type = 0, and if it is an answer, type = 1. If the entry is an answer, I update the parentID to the id on the question row.

All I want to do is pull the questions in a SQL query, along with a number that has the total number of answers to the question - for e.g. a result row would look like

[email protected] "how do I do this?" 4

Where (4) would be the total answers to my question. I want to do this in a single query - without having to pull the questions first, and then run repeated queries for each question Id to find the count of the answers.

How do I do this?

Thank you all,

(PS - is there a way to do the query in Linq expressions? I would do that instead of a stored procedure if I could)

Answer 1

select ft1.poster, ft1.title, count(ft2.id)
    from ForumTable ft1
        left join ForumTable ft2
            on ft1.id = ft2.parentID
                and ft2.type = 1
    where ft1.type = 0
    group by ft1.poster, ft1.title

Answer 2

Can you try this and see if it meets your needs?

SELECT poster, title, ISNULL(X.NoOfAnswers,0)
FROM forum LEFT OUTER JOIN
(SELECT parentId, COUNT(id) as NoOfAnswers
FROM forum
GROUP BY parentId) X
ON forum.id = X.parentId
WHERE forum.type = 0

Answer 3

select A.poster,a.acontent,count(B.parentId) as Answers from forum B, forum A where B.atype=1 and B.parentId=A.id group by a.id,a.poster,a.acontent