1

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.

user11073489
  • 117
  • 2
  • 8

3 Answers3

1

Sounds like a data trap, the Cartesian product https://en.wikipedia.org/wiki/Cartesian_product

Is there anything to bridge the two tables? Like a common table between the two that we can join to? Instead of N:N
Table A would be something in common with the notes table (B) and tags table (C) we could have Table A join to Table B as 1:N and Table A also join to C as 1:N

Then you could stitch the two separate facts together with a common table

VAI Jason
  • 534
  • 4
  • 14
  • It's actually a single table that contains the many-to-many relationship; I have a table for tags, and I have a table for notes. They are linked in the 3rd table which has all the links between the tags and the notes. I am trying to filter the 3rd table – user11073489 Jun 28 '19 at 13:51
0

Try something like this:

; with cteTagList as 
    (select 'Example' idtag
    union select 'SecondExample'
    --...
    union select 'LastExample'
    )
select t.idnote
from table t inner join cteTabList l on l.idtag = t.idtag
group by t.idnote
having count(*) = [NUMBER_OF_SEARCH_TAGS]

Where you generate a CTE (Common Table Expression) which contains all the search tags. Join it with the many-to-many relation table, and only select those notes that hat count equal to the number of search tags inputed by the user, noted [NUMBER_OF_SEARCH_TAGS] in the query

lakta
  • 278
  • 1
  • 9
0

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'))
jefftrotman
  • 1,059
  • 7
  • 16
  • Hey, thanks for the answer! Unfortunately, the problem is that this filters for any idnote that has either of the 3 tags, whereas I need to filter for the idnotes that have all of the 3 tags – user11073489 Jun 28 '19 at 16:13
  • OK - I didn't understand that. I changed the answer to something that would work for all 3. Now that I understand, not sure if this meets your "simplicity" requirements for code generation (depending on how many tags someone can select at one time), but I believe this will work. – jefftrotman Jun 28 '19 at 17:04
  • Thanks, that actually seems to work pretty well! It's easily code generated because all I have to do is append a new 'and' clause everytime a further filter is applied. – user11073489 Jun 28 '19 at 17:38