Write sql query, two tables, INNER JOIN?

Question

SQL

I have a users table with id and login fields
AND a comments table (Comments about the user)
id_user1, ud_user2, comment, comment_type.
id_user2 in this table is the user that was commented on.
comments has column comment_type = 0 or 1.
I need to display each user and the number of positive and negative comments. i.e. quantity where comment_type = 0 and quantity where comment_type=1
I thought it was something like this, but it does not work

SELECT users.id, users.login, COUNT(c.id_user2) as negative, COUNT(c1.id_user2) as positive
 FROM users 
 INNER JOIN comments c on users.id=c.id_user2 WHERE c.commentype=0 
 INNER JOIN comments c1 ON users.id = c1.id_user2 WHERE c1.commentype=1
 GROUP BY users.id, users.login

Answer 1

Here's an option

select u.login, (select count(*) from comments c where u.id=c.id_user1 and c.comment_type=0) one, 
(select count(*) from comments c where u.id=c.id_user1 and c.comment_type=1) two
from users u