SQL how to search a many to many relationship

sql search many-to-many relational-division
Josh Moore picture Josh Moore · Dec 8, 2008 · Viewed 9.8k times · Source

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

Tony Andrews picture Tony Andrews · Dec 8, 2008

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

Related questions

Search All Fields In All Tables For A Specific Value (Oracle)

Is it possible to search every field of every table for a particular value in Oracle? There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. …

Read More
Find a string by searching all tables in SQL Server Management Studio 2008

Is there any way to search for a string in all tables of a database in SQL Server Management Studio 2008? I want to search for string say john. The result should show the tables and their respective row that contain …

Read More
MySQL search and replace some text in a field

What MySQL query will do a text search and replace in one particular field in a table? I.e. search for foo and replace with bar so a record with a field with the value hello foo becomes hello bar.

Read More