Select from table1 WHERE table2 contains ALL search parameters

Question

I have two tables (notes and tags). Tags has a foreign key to notes. There may be several tag records to a single note record.

I'm trying to select only the notes that contain all of the desired tags.

SELECT notes.*, tags.* FROM notes LEFT JOIN tags ON notes.id = tags.note_id 
WHERE {my note contains all three tags I would like to search on}

Using WHERE tag.name IN ('fruit','meat','vegetable') will bring back all the notes that have a "fruit", "meat", OR "vegetable" tag. I only want to return notes that have all three "fruit", "meat", AND "vegetable" tags.

I'm ok to bring back multiple records (the query above would yield a record for each tag).

I need help with my where clause. Is it possible to do this without a sub-select?

Answer 1

If it is not too late, wouldn't it be better to have a NoteTag table - so you will have notes, tags, notetag tables and you can use simple queries and AND operator to find what you want ?)

Answer 2

Without a subselect, as per request:

SELECT  notes.*
FROM    notes
JOIN    tags
ON      tag.note = notes.id
        AND tag.name IN ('fruit','meat','vegetable')
GROUP BY
        notes.id
HAVING  COUNT(*) = 3

More efficient method would be:

SELECT  notes.*
FROM    (
        SELECT  to.note
        FROM    tags to
        WHERE   to.name = 'meat'
        AND     EXISTS
                (
                SELECT  NULL
                FROM    tags ti
                WHERE   ti.note = to.note
                        AND to.name IN ('fruit', 'vegetable')
                LIMIT 1, 1
                )
        ) t
JOIN    notes
ON      note.id = t.note

The trick here is to put the search on the most selective tag ('meat' in my example) on the first place.

Answer 3

Assuming tags(note_id, tag) is declared UNIQUE or PK, then you can use:

SELECT note_id, COUNT(tag) FROM tags
WHERE tag IN ('fruit', 'vegetable', 'meat')
GROUP BY note_id
HAVING COUNT(tag) >= 3

Further answer based on OP's comment below. To get all tags for the records that match:

SELECT * FROM tags
INNER JOIN
(
SELECT note_id, COUNT(tag) FROM tags
WHERE tag IN ('fruit', 'vegetable', 'meat')
GROUP BY note_id
HAVING COUNT(tag) >= 3
) search_results
ON search_results.note_id = tags.note_id