SQL how to search a many to many relationship

问题:

I have a database with two main tables notes and labels. They have a many-to-many relationship (similar to how stackoverflow.com has questions with labels). What I am wondering is how can I search for a note using multiple labels 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 will find all the notes that are associated with labels "one" and "two"?

回答1:

To obtain the details of notes that have both labels 'One' and 'Two':

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

回答2:

Note: I haven't actually tested this. It also assumes you have a many-to-many table named notes_labels, which may not be the case at all.

If you just want the notes that having any of the labels, it's 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 want the notes that have ALL of the labels, there's a little extra work

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 being the number of tags you were searching for. This is assuming that the link table has both ID columns as a compound primary key.

回答3:

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

Of course, replace with your actual column names, hopefully my assumptions about your table were correct.

And actually there's a bit of possible ambiguity in your question thanks to English and how the word 'and' is sometimes used. If you mean you want to see, for example, a note tagged 'one' but not 'two', this should work (interpreting your 'and' to mean, 'show me all the notes with label 'one' and/plus all the notes with label 'two'). However, if you only want notes that have both labels, this would be one way to go about it:

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, the Monday gears in my brain are a bit slow...looks like I should've wiki'd it!

回答4:

Something like this... (you'll 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.

回答5:

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

You should then use an inner join to join the tables together

1. Join the labels table with the bind-table (many-to-many table)
2. Join the notes table with 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)

That way, you have connected both tables together.

Now what you need to add is the where clause...but I'll leave that up to you.

回答6:

You say nothing about how this many-to-many relationship is realised. I assume that the labels table has is Labels(noteid: int, label: varchar) - 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 with your column names, and insert the proper placeholders for the labels.

回答7:

If you just need a list, you can use where exists to avoid duplication. If you have multiple tags against a node in your selection criteria you will get duplicate rows in the result. Here's an example of 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