CODEWITHSUNDEEP
mysql
Atul Sharma
Atul Sharma

Reputation: 87

Count on multiple fields in multiple join on mysql select query

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

Upvotes: 0

Views: 94

Answers (2)

Pankaj katiyar
Pankaj katiyar

Reputation: 464

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

Upvotes: 0

Dhaval Bhavsar
Dhaval Bhavsar

Reputation: 495

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

Upvotes: 0

Related Questions