Clever Geek Handbook

SQL how to search for many-to-many relationships

I have a database with two main tables notes

and labels

. They have many-to-many relationships (similar to how stackoverflow.com has tagged questions). I am wondering how can I search for a note using multiple tags using SQL?

For example, if I have a note "test" with three labels "one", "two" and "three", and I have a second note "test2" with labels "one" and "two", what is the SQL query that find all notes associated with tags one and two?

+5


source to share


7 replies


To get the details of notes that have both "One" and "Two" labels:



select * from notes
where note_id in
( select note_id from labels where label = 'One'
  intersect
  select note_id from labels where label = 'Two'
)
+8


source 


select * from notes a
inner join notes_labels mm on (mm.note = a.id and mm.labeltext in ('one', 'two') )

Replace your actual column names of course, hopefully my assumptions about your table were correct.

And there is actually a bit of possible ambiguity in your question thanks to the English language and the way the word "and" is sometimes used. If you mean you want to see, for example, a note labeled "one" but not "two", this should work (interpret your "and" means "show me all notes labeled" one "and / plus all notes tagged "two") However, if you only want two-tagged notes, this is one way:



select * from notes a
where exists (select 1 from notes_labels b where b.note = a.id and b.labeltext = 'one')
     and exists (select 1 from notes_labels c where c.note = a.id and c.labeltext = 'two')

Edit: thanks for the suggestions everyone, Monday gears in my brain are a little slower ... looks like I should have wiki'd this!

+1


source


Note. I haven't really tested this. It also assumes that you have a many-to-many table named notes_labels, which may or may not exist.

If you just want the notes to have any labels it would be something like this

SELECT DISTINCT n.id, n.text
FROM notes n
INNER JOIN notes_labels nl ON n.id = nl.note_id
INNER JOIN labels l ON nl.label_id = l.id
WHERE l.label IN (?, ?)

If you need notes that have ALL the shortcuts, there is a little extra work to do

SELECT n.id, n.text
FROM notes n
INNER JOIN notes_labels nl ON n.id = nl.note_id
INNER JOIN labels l ON nl.label_id = l.id
WHERE l.label IN (?, ?)
GROUP BY n.id, n.text
HAVING COUNT(*) = 2;

? being a SQL placeholder and 2 is the number of tags you were looking for. This assumes that the reference table has both ID columns as a composite primary key.

+1


source


Something like this ... (you will need another link table)

SELECT *
FROM Notes n INNER JOIN NoteLabels nl
ON n.noteId = nl.noteId
WHERE nl.labelId in (1, 2)

Edit : The NoteLabel table will have two columns, noteId and labelId, with a composite PK.

0


source


Assuming you have a normalized database, you should have another table between notes

andlabels

Then you should use inner join

to join tables together

  • Join table labels

    with binding table (many-to-many table)
  • Join the table notes

    to the previous query

Example:

select * from ((labels l inner join labels_notes ln on l.labelid = ln.labelid) inner join notes n on ln.notesid = n.noteid)

This way you have linked both tables together.

Now you need to add a sentence where

... but I'll leave that up to you.

0


source


You are not saying anything about how this many-to-many relationship is implemented. I presume there are Labels (noteid: int, label: varchar) in the labels table - with a primary key spanning both?

SELECT DISTINCT n.id from notes as n, notes_labels as nl WHERE n.id = nl.noteid AND nl.text in (label1, label2);

Replace the column names and insert appropriate labels for the labels.

0


source


If you just want a list, you can use where exists

to avoid duplication. If you have multiple tags against a node in your selection criteria, you end up with duplicate lines as a result. Here's an example where exists

:

create table notes (
       NoteID int not null primary key
      ,NoteText varchar (max)
)
go

create table tags (
       TagID int not null primary key
      ,TagText varchar (100)
)
go

create table note_tag (
       NoteID int not null
      ,TagID int not null
)
go

alter table note_tag
  add constraint PK_NoteTag
      primary key clustered (TagID, NoteID)
go

insert notes values (1, 'Note A')
insert notes values (2, 'Note B')
insert notes values (3, 'Note C')

insert tags values (1, 'Tag1')
insert tags values (2, 'Tag2')
insert tags values (3, 'Tag3')

insert note_tag values (1, 1) -- Note A, Tag1
insert note_tag values (1, 2) -- Note A, Tag2
insert note_tag values (2, 2) -- Note B, Tag2
insert note_tag values (3, 1) -- Note C, Tag1
insert note_tag values (3, 3) -- Note C, Tag3
go

select n.NoteID
      ,n.NoteText
  from notes n
 where exists
       (select 1
          from note_tag nt
          join tags t
            on t.TagID = nt.TagID
         where n.NoteID = nt.NoteID
           and t.TagText in ('Tag1', 'Tag3'))


NoteID      NoteText
----------- ----------------
1           Note A
3           Note C
0


source






More articles:


All Articles