MySQL LEFT JOIN issue with three WHERE statements

Question

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?

Answer 1

nt_tag_id = 10 AND nt_tag_id = 11 this will always return false

use nt_tag_id in (10,11) instead

if you need both tags there is answer below with joining tag table twice

Answer 2

This will return only notes that have both tag_id 10 and 11.

SELECT notes.*
FROM notes
INNER JOIN notes_tags a ON notes.note_id = a.nt_note_id
                        AND a.nt_tag_id = 10
INNER JOIN notes_tags b ON notes.note_id = b.nt_note_id
                        AND b.nt_tag_id = 11

Answer 3

Assuming you want to identify notes which have related notes_tags records with nt_tag_ids 10 and nt_tag_ids of 11, then the outer join is just making the DBMS work harder than it needs to.

This seems the most obvious solution:

SELECT * FROM notes n
WHERE EXISTS (SELECT 1 FROM notes_tags nt
   WHERE n.note_id=nt.nt_note_id
   AND nt.nt_tag_id=10)
AND EXISTS (SELECT 1 FROM notes_tags nt2
   WHERE n.note_id=nt2.nt_note_id
   AND nt2.nt_tag_id=11);

An alternative approach would be:

SELECT n.*, COUNT(*) 
FROM notes n,
notes_tags nt
WHERE n.note_id-nt.nt_note_id
AND nt.nt_tag_id IN (10,11)
GROUP BY .... /* need to add these */
HAVING COUNT(*)=2;

Note this presupposes that their is a unique constraint on nt_note_id and nt_tag_id in the notes_tags table.

But both the above will only return the data in the notes table. If you need to extract the corresponding rows from MN decomposition table:

SELECT *
FROM notes n,
notes_tags nt,
notes_tags nt2
WHERE n.note_id=nt.nt_note_id
AND n.note_id=nt2.note_id
AND nt.nt_note_id=nt2.nt_note_id /* can help the optimizer come up with a faster query */
AND nt.nt_tag_id=10
AND nt2.nt_tag_id=11

You might want to add aliases to the output columns to make parsing the response a bit easier.

Answer 4

You are using 'AND' instead of that use 'OR' in where clause 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 OR nt_tag_id = 11)

You can use IN also like this if you are going to add more nt_tag_id.

SELECT * FROM notes_tags LEFT JOIN notes ON note_id = nt_note_id WHERE note_uid IN ( 1 ) AND nt_tag_id IN (10, 11)