I'm having troubles making a query that, given a table with posts and one with postsvotes where postsvotes contains the id of the post and the type of vote ('UP' or 'DOWN'), outputs the number of 'UP' votes and 'DOWN' votes for each post.
I tried the following query using views:
DROP VIEW P, U, D;
CREATE VIEW P AS SELECT p.id, p.title, p.content FROM posts as p, users as u WHERE u.id = p.userId GROUP BY p.id ORDER BY p.datetime DESC;
CREATE VIEW U AS SELECT count(*) as Uvotes, postId FROM postsvotes, posts WHERE posts.id = postsvotes.postId AND postsvotes.type = 'UP' GROUP BY postId;
CREATE VIEW D AS SELECT count(*) as Dvotes, postId FROM postsvotes, posts WHERE posts.id = postsvotes.postId AND postsvotes.type = 'DOWN' GROUP BY postId;
SELECT Uvotes, Dvotes, u.postId as postId FROM U, D WHERE u.postId = d.postId GROUP BY postId;
Assuming the postvotes table only contains posts where there is atleast one upvote or a downvote, you can try the query below.
--Get upvote and downvote count for posts in the postvotes table SELECT postId ,count(case when type = 'UP' then 1 end) upvotes ,count(case when type = 'DOWN' then 1 end) downvotes FROM postvotes GROUP BY postId UNION ALL --Get posts that have no votes at all SELECT Id, 0, 0 FROM posts p WHERE NOT EXISTS (select 1 from postvotes where postId = p.Id)