Fetching Items with Multiple Tags in a Tag-based Search

This post is a next step for the post Implementation for tag based mail system by JOINS using MySQL Database. If you haven’t read it before reading this, please do so in order to understand the context of this. 😊

Guys, today something struck my mind. This method can tag multiple, but can retrieve only mails from a single label. I was wondering, how to retrieve if I want to get mails from more than one label. Today morning, I figured out the way... 😊

SELECT *  
FROM `Mails`  
WHERE `Mails`.`Id`  
IN (  
    SELECT `MailId`
    FROM `MailLabel`
    WHERE `LabelName`
    IN (
        'inbox', 'office'
    )
    GROUP BY `mailid`
    HAVING COUNT ( * ) = 2
);

This query does the magic. If you break it, you can see we are selecting all the mails, where we are filtering with the mails with ID corresponding to result of the following SELECT statement:

SELECT `MailId`  
  FROM `MailLabel`
  WHERE `LabelName`
  IN (
    'inbox', 'office'
  )
  GROUP BY `MailId`
  HAVING COUNT( * ) = 2;

If we explore this statement, it returns all the rows with the tags specified. Here, we have specified two tags. The result set is grouped by the mail IDs and once it is done, it returns only unique values of mail IDs and we also have a constraint of the count of the returned mail ID rows should be equal to 2, which is the sum of the number of tags specified. Try it out and do say your results... 😁