MySQL Multiple Inner Joins

[gavola]

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!

[Kruptein]

You can actualy doing multiple inner joins after each other:

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
                        INNER JOIN (SELECT ...) as ... on ... = ...
                        INNER JOIN ...
		GROUP by P.post_ id

I hope this helps

[gavola]

You can actualy doing multiple inner joins after each other:

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
                        INNER JOIN (SELECT ...) as ... on ... = ...
                        INNER JOIN ...
		GROUP by P.post_ id

I hope this helps

But what do I select? Do I need to change the FROM parameter to from votes, commments and flag?

I've tried what you said - but got no luck

SELECT P.post_id, P.post_content, P.post_date, sum(T.vote_tally) as total_vote, count(distinct C.comment_id), count(distinct F.flag_id)
		FROM votes as T, comments as C, flag as F
		INNER JOIN (
		SELECT *
		FROM posts tp
		GROUP BY tp.post_id
		) as P on P.post_id = T.post_id
                 INNER JOIN (
                SELECT * from posts tp1
                GROUP by tp1.post_id
               ) as P on P.post_id = C.post_id
                INNER JOIN (
                SELECT * from posts tp2
                GROUP by tp2.post_id
               ) as P on P.post_id = F.post_id
		GROUP by P.post_ id

Any suggestions?

[Kruptein]

You always have to give an other name:
you are doing everywhere: (...) As P

[gavola]

You always have to give an other name:
you are doing everywhere: (...) As P

Is this better?

SELECT P.post_id, P.post_content, P.post_date, sum(T.vote_tally) as total_vote, count(distinct C.comment_id), count(distinct F.flag_id)
		FROM votes as T, comments as C, flag as F
		INNER JOIN (
		SELECT *
		FROM posts tp
		GROUP BY tp.post_id
		) as P on P.post_id = T.post_id
                 INNER JOIN (
                SELECT * from posts tp1
                GROUP by tp1.post_id
               ) as P1 on P1.post_id = C.post_id
                INNER JOIN (
                SELECT * from posts tp2
                GROUP by tp2.post_id
               ) as P2 on P2.post_id = F.post_id
		GROUP by P.post_ id

[Kruptein]

That is better as the previous version, but does it work?

[gavola]

That is better as the previous version, but does it work?

I have not tried that yet - however, I'm concerned about the sum function - because I believe that it won't show up if the sum is null (i.e no votes). Maybe I'm wrong?

[Kruptein]

Force the vote row to be not null, and set the default value to zero.