Querying a Many-to-Many Linking Table

I have a linking table for a many-to-many relationship, with the fields –

• idNote
• idTag

I would like to filter for all the tags that are associated with the notes that contain a specified number of tags.

For example, if I select the tags ‘Running‘, ‘Form‘, and ‘Times‘, I would then like to see all the tags that are associated with the notes that have these 3 tags.

This process will be used by the user on the front end to refine the results they are looking for, so I need to be able to generate this SQL with code (node.js), with the filtering by tags potentially occurring many times over.

I have the below SQL code, which can query for two tags, but there are some problems with it:

1. It does not seem efficient
2. It can not be easily generated through code if another layer of filers needs to be added

SELECT DISTINCT idtag FROM table WHERE idnote IN (SELECT idnote FROM 
(SELECT * FROM table WHERE idnote IN (SELECT idnote FROM table WHERE idtag 
= 'Example')) as t1 where t1.idtag = 'SecondExample');

I am hoping for some suggestions on how to improve the efficiency of this code, as well as turning the sql statement into something that is easily code generateable.

Answer

I used your example of 'Running','Form','Times' as the specified set of tags.

select distinct idTag from table 
where idNote in (select idNote from table where idTag in ('Running'))
and idNote in (select idNote from table where idTag in ('Form'))
and idNote in (select idNote from table where idTag in ('Times'))