Exclude the user on the count if the post is created by the same user

Question

I have the following scenario in my system:

a member:

CREATE TABLE `member` (
  `memberid` int(11) NOT NULL,
  `email` text
);

creates the protocols:

CREATE TABLE `protocol` (
  `protocolid` int(11) NOT NULL,
  `createdby` int(11) NOT NULL,
  `status` varchar(256) DEFAULT NULL
) ;

member can create a feedback post on the protocols

CREATE TABLE `protocolpost` (
  `protocolid` int(11) NOT NULL,
  `protocolpostid` int(11) NOT NULL,
  `createdby` text
  ) ;

member can reply to the feedback

CREATE TABLE `protocolpostcomment` (
  `protocolpostcommentid` int(11) NOT NULL,
  `protocolpostid` int(11) NOT NULL,
  `commentedby` varchar(256) DEFAULT NULL,
  `hasfeedbackreplyviewed` tinyint(1) DEFAULT NULL
) ;

I wanted to get the total count of replies from all post comments made on a protocol created by a member, excluding counts of a user who created that protocol, and comments made by the author of the post.

I have written this query so far, but this query returns all the post comments, I wanted to exclude the reply done by the feedback creator.

SELECT 
       protocols.*,
       protocolFeedbackReply.*,
       protocolfeedback.*
FROM   protocolpost AS protocolfeedback
       JOIN protocol AS protocols
         ON protocols.protocolid = protocolfeedback.protocolid
       JOIN protocolpostcomment AS protocolFeedbackReply
         ON protocolfeedback.protocolpostid =
            protocolFeedbackReply.protocolpostid
WHERE  protocols.createdby = 1038
       AND protocols.status = "published"
       AND protocolFeedbackReply.hasfeedbackreplyviewed = 0
       AND protocolfeedback.createdby NOT LIKE Concat('%', (SELECT email
                                                            FROM   member
                                                            WHERE
                                               memberid = 1038),
                                               '%');

I have attached a dbfiddle here:

In the dbfiddle example only the comment that is done by the user nwxaofrc@tempemail.com, , should be on the count.

score 1 · Answer 1 · answered May 19 '22 at 13:02

Thank you for your very good description. Your query is difficult to read and might be simplified if possible. Anyway, within your NOT LIKE condition, it seems you need to check protocolFeedbackReply.commentedby instead of protocolfeedback.createdby, see db<>fiddle

Exclude the user on the count if the post is created by the same user

1 Answers1