Welcome, guest | Sign In | My Account | Store | Cart

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
1
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.

Created by Kaushik Ghose on Sat, 11 Jun 2011 (MIT)
Text recipes (14)
Kaushik Ghose's recipes (15)

Required Modules

  • (none specified)

Other Information and Tasks