CODEWITHSUNDEEP
sqlsqlite
dank
dank

Reputation: 851

sql JOIN with boolean on WHERE

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.

Upvotes: 2

Views: 380

Answers (3)

PinnyM
PinnyM

Reputation: 35531

To improve on @GordonLinoff's answer:

select * from n where nid IN 
 (select nid
  from (select * from nk where kid in (1,2)) s
  group by nid
  having count(distinct s.kid) = 2)

This will get those records that have kid matching 1 and 2 even if other kid values might be present (3,4,etc). sqlfiddle here.

Upvotes: 1

bsmoo
bsmoo

Reputation: 1049

select * from n LEFT JOIN nk ON nk.nid = n.nid WHERE nk.kid in (1,2)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269963

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.

Upvotes: 1

Related Questions