MySQL: aliased fields return value of just one field

[eluser]codex[/eluser]
Consider this query:

Code:

SELECT fc.*, COUNT( fp.forum_post_id ) AS total_posts,
COUNT( fp2.forum_post_id ) AS total_replies

FROM forum_categories AS fc
LEFT JOIN forum_posts AS fp ON fp.forum_cat_id = fc.forum_cat_id AND fp.post_parent_id = 0
LEFT JOIN forum_posts as fp2 ON fp2.forum_cat_id = fc.forum_cat_id AND fp2.post_parent_id != 0
GROUP BY fc.forum_cat_id

total_posts and total_replies should yield different results, but instead I get the same result for both. Obviously there's something not quite right with this query, but I don't understand what. I have the 2 aliased differently, so what could be the problem?

Are AND's allowed in joins?

[eluser]BizComputing[/eluser]
I suspect your counts are total of fc recs.

Your joins are LEFT JOINS, so count will count NULL fp.forum_post_id's for non-hits

I seem to recall encountering this problem before, but for the life of me I can't remember how I resolved it.

[eluser]codex[/eluser]
[quote author="BizComputing" date="1201739850"]I suspect your counts are total of fc recs.

Your joins are LEFT JOINS, so count will count NULL fp.forum_post_id's for non-hits

I seem to recall encountering this problem before, but for the life of me I can't remember how I resolved it.[/quote]

I actually got it solved. I was doing an all wrong query. But thanks anyway!

(and on to the next problem)

[eluser]BizComputing[/eluser]
If you don't mind me asking, what was the query that worked?

I was thinking problem could have been solved with 1 join and the COUNT against a CASE WHEN

[eluser]codex[/eluser]
[quote author="BizComputing" date="1201741146"]If you don't mind me asking, what was the query that worked?

I was thinking problem could have been solved with 1 join and the COUNT against a CASE WHEN[/quote]

Well, I completely overlooked the fact that I had stored the total_replies in an own field, making the query I was looking for totally irrelevant. I just grabbed that field and that was it (basically). So in short: I can share the query with you, but I doubt you'll gain anything from it