sqlite3 - sql JOIN with boolean on WHERE -

sqlite3 - sql JOIN with boolean on WHERE -

first, please note have little experience sql (in case sqlite3) , title of question may ill-phrased.

suppose have table of notes (n), , table of keywords notes (k), , table associating 1 or more keywords each note (nk). i'd find notes each contain 2 (or more) keywords.

to bit more specific, below (sqlite3) how i've set 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 n(content) values ("note 1"); insert n(content) values ("note 2"); insert k(content) values ("keyword 1"); insert k(content) values ("keyword 2"); insert nk(nid, kid) values (1, 1); insert nk(nid, kid) values (1, 2); insert nk(nid, kid) values (2, 1);

with this, can notes tagged keyword id of 1 with

select * n left bring together nk on nk.nid = n.nid nk.kid=1;

and question how can notes keyword ids 1 and 2. i've done searching on web, i'm afraid knowledge insufficient think of searching terms. hope on site can help , -- importantly -- apologize if silly question.

to improve on @gordonlinoff's answer:

select * n nid in (select nid (select * nk kid in (1,2)) s grouping nid having count(distinct s.kid) = 2)

this records have kid matching 1 , 2 if other kid values might nowadays (3,4,etc). sqlfiddle here.

sql sqlite3