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

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!