Join votes table and sum all votes

I've got two tables. One of them contains quotes and the other one lists all given votes (either +1 or -1) for each quote. For demonstration purposes I've made simplified versions of the two tables:

Quotes

+----+-----------------------------------------------------------------------+
| ID | quote                                                                 |
+----+-----------------------------------------------------------------------+
|  1 | If you stare into the Abyss long enough the Abyss stares back at you. |
|  2 | Don't cry because it's over. Smile because it happened.               |
|  3 | Those that fail to learn from history, are doomed to repeat it.       |
|  4 | Find a job you love and you'll never work a day in your life.         |
+----+-----------------------------------------------------------------------+

Votes

+----+-------+------+
| ID | quote | vote |
+----+-------+------+
|  1 |     1 |   -1 |
|  2 |     1 |   -1 |
|  3 |     3 |    1 |
|  4 |     3 |   -1 |
|  5 |     3 |    1 |
|  6 |     3 |   -1 |
|  7 |     4 |    1 |
|  8 |     4 |    1 |
|  9 |     4 |开发者_开发知识库    1 |
+----+-------+------+

I'd like to list all quotes on my site and show the respective vote count besides. At first, the SQL query should read all quotes and afterwards join the votes table. However, it should finally list the sum of all votes for each quote. The result of the SQL query will therefore look as follows:

+----+-----------------+------+
| ID | quote           | vote |
+----+-----------------+------+
|  1 | If you stare... |   -2 |
|  2 | Don't cry...    | NULL |
|  3 | Those that...   |    0 |
|  4 | Find a job...   |    3 |
+----+-----------------+------+

How does the SQL query look like that does the previously described?

SELECT
    `quotes`.`id` as `ID`,
    `quote`.`quote` as `quote`,
    SUM(`votes`.`vote`) AS `vote`
FROM  `quotes`
    LEFT JOIN `votes`
        ON `quotes`.`id` = `votes`.`quote`
GROUP BY `quotes`.`id`

should do the trick.

Assuming id columns are primary keys (they are unique for each record).

SELECT
  ID, quote, (SELECT sum(vote) from votes where votes.quote=quotes.ID)
FROM 
  quotes 

SELECT q.id, q.quote, SUM(v.vote ) as summ
FROM Quotes q 
LEFT JOIN Votes v ON q.id=v.quote 
GROUP BY q.id, q.quote                                                                 ;

The following should work. The left join means the vote summaries are included even if there is no line.

select ID, quote, total_votes from quotes
left join 
(select quote, sum(vote) as total_votes from quotes
 group by quote) ) as vote_totals
on quotes.ID = vote_totals.quote

Select q.id, q.quote, sum(v.vote) from
quotes q
inner join votes v on
q.id= v.quote
group by v.quote

SELECT Quotes.ID ID, Quotes.QUOTE QUOTE, SUM(Votes.vote) VOTE FROM Quotes LEFT JOIN Votes ON Votes.quote = Quotes.id