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!