Count on multiple fields in multiple join on mysql select query

Question

I want display total comments, total likes, total dislikes on a note in note detail page. Following are three tables from which am fetching data.

tbl_note_actions
noteaction_note_id int(11) NOT NULL,
noteaction_user_id int(11) NOT NULL, noteaction_type tinyint(1) NOT NULL COMMENT '1->like, 0->dislike',
PRIMARY KEY (noteaction_note_id,noteaction_user_id)

tbl_notes_comments
notepost_id int(11) NOT NULL AUTO_INCREMENT,
notepost_note_id int(11) NOT NULL, notepost_user_id int(11) NOT NULL,
notepost_message text NOT NULL,
notepost_rating tinyint(1) NOT NULL,
notepost_parent_id int(11) NOT NULL,
notepost_added_on datetime NOT NULL,
notepost_active tinyint(1) NOT NULL DEFAULT '1',
notepost_deleted tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (notepost_id)

tbl_notes
note_id int(11) NOT NULL AUTO_INCREMENT,
note_notetype_id int(11) NOT NULL,
note_user_id int(11) NOT NULL,
note_description text NOT NULL, PRIMARY KEY (note_id)

It showing wrong counts for total likes, total dislikes and total comments.

Here is my query:

SELECT
    note_id,
    note_notetype_id,
    note_user_id,
    note_name,
    note_description,
    COUNT( DISTINCT notepost_note_id) AS totComments,
    note_id,
    count(if(noteaction_type = 1, noteaction_note_id, NULL)) AS totlikes,
    count( if(noteaction_type = 0, noteaction_note_id, NULL)) AS totdislikes
FROM `tbl_notes`
LEFT OUTER JOIN `tbl_users`
    ON user_id= note_user_id
INNER JOIN `tbl_courses`
    ON course_id = note_course_id
INNER JOIN `tbl_universities`
    ON university_id = note_university_id
INNER JOIN `tbl_note_types`
    ON notetype_id = note_notetype_id
LEFT OUTER JOIN `tbl_note_actions`
    ON noteaction_note_id = note_id
LEFT OUTER JOIN `tbl_notes_posts`
    ON notepost_note_id = note_id
WHERE `note_id` = '4'
GROUP BY note_id, notepost_id

which results likes 1, dislikes 1 and comments 1 but actually it should be likes 1, dislikes 1 and comments 4.

Please help me to resolve it

Thanks

table fields have already distinct name so I think no need to use alias names. Please update me if I am wrong. :) — Atul Sharma
– Atul Sharma, Commented Jun 23, 2016 at 10:01

Dhaval Bhavsar · Accepted Answer · 2016-06-23 10:18:09Z

0

Dear i want to know one thing why you using DISTINCT COUNT( DISTINCT notepost_note_id) AS totComments because if i have one post which have multiple comment then you use DISTINCT it will return 1 value always for that post

Remove DISTINCT and check

answered Jun 23, 2016 at 10:18

Dhaval Bhavsar

4955 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dhaval Bhavsar Over a year ago

can you please give me field of tbl_notes_posts

Atul Sharma Over a year ago

I apologies tbl_notes_comments is actually tbl_notes_posts

Dhaval Bhavsar Over a year ago

please replace COUNT( DISTINCT notepost_note_id) AS totComments, with these one COUNT( DISTINCT notepost_id) AS totComments,

Atul Sharma Over a year ago

I have tried this time comments dhows accurate count i.e. 4 but likes and dislikes showing 24 each which should be Count: 4 Likes 2 Dislikes: 3

Atul Sharma Over a year ago

Let us continue this discussion in chat.

Pankaj katiyar · Accepted Answer · 2016-06-23 10:45:32Z

0

try this It may help you

         SELECT
    note_id,
    note_notetype_id,
    note_user_id,
    note_name,
    note_description,
    noteCount.totalComment AS totComments,
    note_id,
    count(if(noteaction_type = 1, noteaction_note_id, NULL)) AS totlikes,
    count( if(noteaction_type = 0, noteaction_note_id, NULL)) AS totdislikes
FROM `tbl_notes`

LEFT JOIN (
                         SELECT note_id,count(notepost_note_id) as totalComment
                         FROM tbl_notes  
                         GROUP BY note_id
         ) AS noteCount ON noteCount.note_id=tbl_notes.note_id

answered Jun 23, 2016 at 10:45

Pankaj katiyar

46410 silver badges26 bronze badges

1 Comment

Pankaj katiyar Over a year ago

IFNULL(COUNT( DISTINCT notepost_note_id), 0) AS "totComments",

Collectives™ on Stack Overflow

Count on multiple fields in multiple join on mysql select query

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related