Select from table1 WHERE table2 contains ALL search parameters

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 
开发者_如何学JAVAWHERE {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?

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

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.

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