Search code examples
sqlsearchmany-to-manyrelational-division

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

Solution

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