Hello everyone - first time poster here, long time reader.
I have a minor issue that appear to simple to fix - but I've just hit a road block. I would like to create a three way inner join to display all the information I need in one SQL statement.
Let me describe the issue. I have a 4 tables that contain normalized data (posts, votes, flag, and comments).
Posts is the main table, and has the following fields (post_id, post_content, post_date)
Votes is the table that contains all the votes for each post (vote_id, post_id, vote_up, vote_down, vote_tally)
Vote_tally is just the (vote_up - vote_down), which is the sum of the vote. Kinda redundant, but I needed to display negative numbers.
Flag is the table that contains the user who flag a post (flag_id, post_id, flag_date)
Comments is the comments (comment_id, comment, comment_date, post_id)
All 4 tables share the post_id field - however I would like to create a SQL statement that joins all the tables together and returns these fields:
post_id, post_content, post_date, (the sum of vote_tally), (the count of flag_id), (the count of the comments)
Can this be obtained? Or perhaps you have a better way to had the schema for this type of project.
I've included my SQL which gets me the first 4 desired results, but I'm stuck on getting the rest. Any help?
SELECT P.post_id, P.post_content, P.post_date, sum(T.vote_tally) as total_vote FROM votes as T INNER JOIN ( SELECT * FROM posts tp GROUP BY tp.post_id ) as P on P.post_id = T.post_id GROUP by P.post_id
Thanks in advance for your help!