Say we have a table (notes) containing rows we want to select. Each note has one or more keywords (stored in a table of the same name). We want to select notes that have a conjunction of keywords (AND). notes and keywords are linked through a foreign key table notes_keywords. The following SQL statement allows us to do this
Text, 3 lines
Download
Copy to clipboard1 2 3 | SELECT date, title FROM notes WHERE id IN
(SELECT kn.note_id FROM keywords_notes AS kn WHERE kn.keyword_id IN (1,2,3,4)
GROUP BY kn.note_id HAVING COUNT(*) = 4)
|
The query first does a union, pulling all notes in that have any one of the four keywords, and then the GROUP BY clause allows us to COUNT how many of the four keywords each note has and HAVING COUNT lets us select only those that have four keywords.
