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 i

Solution 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'intersectselect note_id from labels where label ='Two'
)

Solution 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

SELECTDISTINCT n.id, n.textFROM 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.textFROM 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 (?, ?)
GROUPBY 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.

Solution 3:

select*from notes a
innerjoin 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
whereexists (select1from notes_labels b where b.note = a.id and b.labeltext ='one')
     andexists (select1from 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!

Solution 4:

Something like this... (you'll need another link table)

SELECT*FROM Notes n INNERJOIN 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.

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