SQL how to search a many to many relationship

Question

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"?

Answer 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'
)

Answer 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.