MYSQL - Finding unread messages for users in emails

Question

I have a simple email database with two tables.

EMAIL & EMAIL_MESSAGES

    CREATE TABLE IF NOT EXISTS EMAIL (
      MAIL_NO TINYINT UNSIGNED NOT NULL, 
      BIZ_ID VARCHAR(35) CHARACTER SET 'latin1' COLLATE 'latin1_bin' NOT NULL, 
      FROM_ADD INT UNSIGNED NOT NULL,  -- FOR EMAIL_TYPE EUCP, THIS WILL BE AGENT ID
      TO_ADD INT UNSIGNED DEFAULT NULL, -- FOR EMAIL_TYPE EUCP, THIS WILL BE CANDIDATE_ID
      EMAIL_SUBJECT VARCHAR(75) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' DEFAULT NULL,
      MAIL_STATUS CHAR(1) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT 'A',
      UPDATED_DATE DATETIME ON UPDATE CURRENT_TIMESTAMP,
      EMAIL_TYPE VARCHAR(10) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT NULL, 
      PRIMARY KEY (MAIL_NO,BIZ_ID),
      INDEX idx_EMAIL_id1 (FROM_ADD ASC),
      INDEX idx_EMAIL_id2 (TO_ADD ASC),
      INDEX idx_EMAIL_id3 (BIZ_ID ASC),
      INDEX idx_EMAIL_id4 (MAIL_STATUS ASC),
      INDEX idx_EMAIL_id5 (EMAIL_SUBJECT ASC), 
      INDEX idx_EMAIL_id6 (UPDATED_DATE ASC)  
    )
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8
    COLLATE = utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS EMAIL_MESSAGE (
  MESSAGE_NO TINYINT UNSIGNED NOT NULL, 
  BIZ_ID VARCHAR(35) CHARACTER SET 'latin1' COLLATE 'latin1_bin' NOT NULL, 
  SENDER INT UNSIGNED DEFAULT NULL, 
  MESSAGE TEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' DEFAULT NULL, 
  ATTACHMENT VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT NULL, 
  MESSAGE_STATUS CHAR(1) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT 'U', 
  SEND_LOCATION VARCHAR(20) CHARACTER SET 'latin1' COLLATE 'latin1_bin' DEFAULT NULL, 
  READ_DATE DATETIME DEFAULT NULL, 
  SEND_DATE DATETIME DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (MESSAGE_NO,BIZ_ID),
  INDEX idx_EMAIL_MESSAGE_id1 (SENDER ASC),
  INDEX idx_EMAIL_MESSAGE_id2 (MESSAGE_STATUS ASC),
  CONSTRAINT fk_EMAIL_MESSAGE_C1
    FOREIGN KEY (BIZ_ID)
    REFERENCES EMAIL (BIZ_ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

Populate Data

INSERT INTO `EMAIL` (`MAIL_NO`,`BIZ_ID`,`FROM_ADD`,`TO_ADD`,`EMAIL_SUBJECT`,`MAIL_STATUS`,`UPDATED_DATE`,`EMAIL_TYPE`) VALUES (1,'10033-10001-10001',10001,10110,'Pacific','A','2015-09-24 10:12:29','EUCP');
INSERT INTO `EMAIL` (`MAIL_NO`,`BIZ_ID`,`FROM_ADD`,`TO_ADD`,`EMAIL_SUBJECT`,`MAIL_STATUS`,`UPDATED_DATE`,`EMAIL_TYPE`) VALUES (1,'10033-10111-10003',10111,10110,'Atlantic','A','2015-09-24 09:48:05','EUCP');
INSERT INTO `EMAIL` (`MAIL_NO`,`BIZ_ID`,`FROM_ADD`,`TO_ADD`,`EMAIL_SUBJECT`,`MAIL_STATUS`,`UPDATED_DATE`,`EMAIL_TYPE`) VALUES (1,'10033-10112-10004',10112,10110,'Artic','A','2015-09-24 09:54:43','EUCP');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (1,'10033-10001-10001',10001,'I need an Apple',NULL,'U','TBD',NULL,'2015-09-24 10:12:29');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (1,'10033-10111-10003',10111,'I have a product that you might be keen to have a look at>',NULL,'R','TBD','2015-09-24 10:10:44','2015-09-24 09:48:05');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (1,'10033-10112-10004',10112,'New products have been launched. Thought you would be interested',NULL,'R','TBD','2015-09-24 10:09:53','2015-09-24 09:54:43');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (2,'10033-10111-10003',10111,'where is this based',NULL,'R','TBD','2015-09-24 10:10:44','2015-09-24 10:09:02');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (2,'10033-10112-10004',10110,'Thanks',NULL,'R','TBD','2015-09-24 10:09:53','2015-09-24 10:08:43');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (3,'10033-10111-10003',10110,'You mean me?',NULL,'R','TBD','2015-09-24 10:10:44','2015-09-24 10:09:26');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (3,'10033-10112-10004',10110,'Any update?',NULL,'U','TBD',NULL,'2015-09-24 10:10:33');
INSERT INTO `EMAIL_MESSAGE` (`MESSAGE_NO`,`BIZ_ID`,`SENDER`,`MESSAGE`,`ATTACHMENT`,`MESSAGE_STATUS`,`SEND_LOCATION`,`READ_DATE`,`SEND_DATE`) VALUES (4,'10033-10111-10003',10111,'Yes',NULL,'U','TBD',NULL,'2015-09-24 10:10:51');

I have tried with the below but it works only if there is one message and fails every other time. Can you figure out where might be the problem?

Email Sender

Select e.MAIL_NO, e.BIZ_ID, e.From_ADD AS AGENT_ID, e.TO_ADD AS CAndIDATE_ID, e.EMAIL_SUBJECT,  e.MAIL_STATUS,
  em.SENDER, ConCAT(uto.USER_FIRST_NAME,' ',uto.USER_LAST_NAME) as USER_NAME,
  if(em.UNREAD_MESSAGE_COUNT is NULL,0,em.UNREAD_MESSAGE_COUNT) as UNREAD_MESSAGE_COUNT From EMAIL e Left Join 
USER_ConFIG uto on uto.USER_ID = e.TO_ADD Left Join 
      (Select COUNT(*) AS UNREAD_MESSAGE_COUNT, SENDER, BIZ_ID From EMAIL_MESSAGE Inner Join EMAIL USING (BIZ_ID)
        Where MESSAGE_STATUS = 'U' And SENDER NOT IN ('10001') GROUP BY SENDER) em
    on em.BIZ_ID = e.BIZ_ID Where 
    e.From_ADD='10111' ;

Email Receiver

Select e.MAIL_NO, e.BIZ_ID, e.From_ADD AS AGENT_ID, e.TO_ADD AS CAndIDATE_ID, e.EMAIL_SUBJECT,  e.MAIL_STATUS,
  em.SENDER,  ConCAT(uFrom.USER_FIRST_NAME,' ',uFrom.USER_LAST_NAME) AS USER_NAME,
  IF(em.UNREAD_MESSAGE_COUNT IS NULL,0,em.UNREAD_MESSAGE_COUNT) AS UNREAD_MESSAGE_COUNT From EMAIL e Left Join 
USER_ConFIG uFrom 
on uFrom.USER_ID = e.From_ADD 
Left Join 
      (Select COUNT(*) AS UNREAD_MESSAGE_COUNT, SENDER, BIZ_ID From EMAIL_MESSAGE Inner Join EMAIL USING (BIZ_ID)
        Where MESSAGE_STATUS = 'U' And SENDER NOT IN ('10002') ) em
    on e.BIZ_ID = em.BIZ_ID 
Where 
    e.TO_ADD="10110" 

Answer 1

I am not quite sure I understand exactly what you are trying to do with your first query, but the sub query results might be a bit odd. You appear to be getting a count of unread messages for each sender with a BIZ_ID as well. But you do not join on BIZ_ID and you do not specify which BIZ_ID to bring back (either from which table or which row of those tables).

However not sure that the sub query is necessary and think it could just be done with a join:-

Select e.MAIL_NO, 
        e.BIZ_ID, 
        e.From_ADD AS AGENT_ID, 
        e.TO_ADD AS CAndIDATE_ID, 
        e.EMAIL_SUBJECT,  
        e.MAIL_STATUS,
        em.SENDER, 
        ConCAT(uto.USER_FIRST_NAME,' ',uto.USER_LAST_NAME) as USER_NAME,
        COUNT(em.MESSAGE_NO) as UNREAD_MESSAGE_COUNT 
FROM EMAIL e 
INNER JOIN USER_ConFIG uto ON uto.USER_ID = e.TO_ADD 
LEFT OUTER JOIN EMAIL_MESSAGE em ON e.BIZ_ID = em.BIZ_ID AND em.MESSAGE_STATUS = 'U' AND em.SENDER NOT IN ('10001') 
WHERE e.From_ADD='10111' 
GROUP BY e.MAIL_NO, 
        e.BIZ_ID, 
        e.From_ADD AS AGENT_ID, 
        e.TO_ADD AS CAndIDATE_ID, 
        e.EMAIL_SUBJECT,  
        e.MAIL_STATUS,
        em.SENDER, 
        USER_NAME;