Member Avatar for 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!

  • 2 Contributors
  • 7 Replies
  • 797 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Kruptein
Member Avatar for 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

Edited by Kruptein because: n/a
Member Avatar for 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?

Member Avatar for Kruptein

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

Member Avatar for 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
Member Avatar for Kruptein

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

Member Avatar for 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?

Member Avatar for Kruptein

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.