Question: First, please note that I have little experience with SQL (in this case sqlite3) and so even the title of this question may be ill-phrased.

Suppose that I have a table of notes (`n`), and a table of keywords for those notes (`k`), and finally a table associating one or more keywords to each note (`nk`). What I'd like to do is to find notes that each contain two (or more) keywords.

To be a bit more specific, below (sqlite3) is how I've set up the database.

```
CREATE TABLE n(nid integer primary key autoincrement, content);
CREATE TABLE k(kid integer primary key autoincrement, content);
CREATE TABLE nk(nkid integer primary key autoincrement, nid, kid);
INSERT INTO n(content) VALUES ("note 1");
INSERT INTO n(content) VALUES ("note 2");
INSERT INTO k(content) VALUES ("keyword 1");
INSERT INTO k(content) VALUES ("keyword 2");
INSERT INTO nk(nid, kid) VALUES (1, 1);
INSERT INTO nk(nid, kid) VALUES (1, 2);
INSERT INTO nk(nid, kid) VALUES (2, 1);

```

With this, I can get notes tagged with a keyword ID of 1 with

```
select * from n LEFT JOIN nk ON nk.nid = n.nid WHERE nk.kid=1;

```

and my question is how I can get notes with keyword IDs 1 *and* 2. I've done some searching on the web, but I'm afraid that my knowledge is insufficient to think of good searching terms. I hope someone on this site can help and -- importantly -- I apologize if this is a silly question.

Answer: 
If you just want notes that have more than two keywords, use aggregation:

```
select nid
from nk
group by nid
having count(*) >= 2

```

If the keywords can be duplicated on the list, use:

```
having count(distinct nk.kid) >= 2

```

To get notes with keywords 1 and 2, use:

```
having max(case when kid = 1 then 1 else 0 end) = 1 and
       max(case when kid = 2 then 1 else 0 end) = 1

```

The `having` clause in this case is just creating a flag whenever it sees one of the values. The logic then determines whether that value has been seen, in the set of keywords for a given note.
`select * from n LEFT JOIN nk ON nk.nid = n.nid WHERE nk.kid in (1,2)`