Get total count in addition of count if user voted

Got three tables I'm joining. submissions, submissions_votes, and users.

I want to find out how many total helpfulVotes there are (which is a sum of the count of all submissions_votes) and I've got that.

I also want to return a count (boolean, rather) of 0 or 1 if the user_id of sv.user_id relates to the submission being viewed.

 SELECT s.*,   u.username,   u.photo as userPhoto,   COALESCE(SUM(sv.up), 0) helpfulVotes   FROM   submissions s   LEFT JOIN submissions_votes sv on s.id = sv.submission_id   INNER JOIN users u   ON s.user_id = u.id

I know I need an additional join (on sv.user_id = u.id) but what would I select? Then would I group by sv.id?

Edit:

users table:

+----------------+------------------------+------+-----+-------------------+-----------------------------+  | Field | Type | Null | Key | Default | Extra |  +----------------+------------------------+------+-----+-------------------+-----------------------------+  | id | int(10) unsigned | NO | PRI | NULL | auto_increment |  | email | varchar(128) | NO | MUL | NULL | |  | username | varchar(23) | NO | | NULL | |  | type | enum('normal','admin') | NO | | normal | |  | about | varchar(255) | NO | | NULL | |  | photo | varchar(32) | NO | | NULL | |  +----------------+------------------------+------+-----+-------------------+-----------------------------+

submissions_votes table:

+---------------+---------------------+------+-----+---------+----------------+  | Field | Type | Null | Key | Default | Extra |  +---------------+---------------------+------+-----+---------+----------------+  | id | int(10) unsigned | NO | PRI | NULL | auto_increment |  | submission_id | int(10) unsigned | NO | MUL | NULL | |  | when | datetime | NO | | NULL | |  | user_id | int(10) unsigned | NO | MUL | NULL | |  | up | tinyint(3) unsigned | NO | | NULL | |  | down | tinyint(3) unsigned | NO | | NULL | |  +---------------+---------------------+------+-----+---------+----------------+

submissions_votes table:

+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+  | Field | Type | Null | Key | Default | Extra |  +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+  | id | int(10) unsigned | NO | PRI | NULL | auto_increment |  | title | varchar(255) | NO | MUL | NULL | |  | slug | varchar(255) | NO | | NULL | |  | description | mediumtext | NO | | NULL | |  | user_id | int(11) | NO | MUL | NULL | |  | created | datetime | NO | | NULL | |  | type | enum('tip','request') | NO | | NULL | |  | thumbnail | varchar(64) | YES | | NULL | |  | removed | tinyint(1) unsigned | NO | | 0 | |  | keywords | varchar(255) | NO | | NULL | |  | ip | int(10) unsigned | NO | | NULL | |  +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+

Answer

I use mssql so don't know for sure if my answer works for mysql. In mssql the query in the question would not work without a group by. if you did leave the group by out,You should include srsv.submission_id in the group by. Assuming you have a constraint that makes sure there is only on submission_vote per submission and user the following should work:

SELECT s.*,   u.username,   u.photo as userPhoto,   COALESCE(SUM(sv.up), 0) helpfulVotes   case when srsv.submission_id is null then 1 else 0 end SelfRelatedVote  FROM   submissions s  LEFT JOIN submissions_votes sv on s.id = sv.submission_id  Left JOIN submissions_votes srsv on s.id = srsv.submission_id and s.user_id = srsv.user_id  INNER JOIN users u  ON s.user_id = u.id

Answer2

I don't think you need an additional join. Just a boolean expression in the select:

SELECT s.*,   u.username,   u.photo as userPhoto,   COALESCE(SUM(sv.up), 0) helpfulVotes,   (sv.user_id = u.id) as SubmissionUserMatches  FROM submissions s LEFT JOIN   submissions_votes sv   on s.id = sv.submission_id INNER JOIN   users u   ON s.user_id = u.id;