MySQL LEFT JOIN issue with three WHERE statements
- by jhat
I am building a note taking app for myself with tag filtering functions, but am having an issue when trying to grab notes with the tags. The tag filter needs to use AND not IN, because it will help better narrow down what I am looking for.
My tables are configured like this:
+ notes note_id | note_title | note_uid
+ tags tag_id | tag_title
+ notes_tags nt_id | nt_note_id | nt_tag_id
The notes_tags table keeps track of all notes' tags.
I am not worried about returning any information about tags, so here is an example LEFT JOIN I am using currently to only get notes with only 1 tag.
SELECT * FROM notes_tags LEFT JOIN notes ON note_id = nt_note_id WHERE note_uid IN ( 1 ) AND nt_tag_id = 10
This query runs perfect, it grabs all of the notes with that single tag. However, I am having issues "pinpointing" my notes using a query like this:
SELECT * FROM notes_tags LEFT JOIN notes ON note_id = nt_note_id WHERE note_uid IN ( 1 ) AND nt_tag_id = 10 AND nt_tag_id = 11
What am I doing wrong with the syntax?
