SQLite query matching only on multiple rows or the same ID

user_1617339 0
Viewed 36

I have a system which tags files with text labels. Each file has several tags. The relevant tables/columns are:

taggings(fileHash,tagId)
tags(tagId,tagText)

How do I find all tagId which have both a row where tagText matches query 1 and another row where it matches query 2? In other words, how can I find each fileHash that has both of two tags when the tags are in separate rows as shown above?

Is there a way to do this for an arbitrary number of matches?

I found this which seems related, but when I try to write a query like that, I get an "Unable to prepare statement" error.

Sample Data

Taggings

fileHash    tagId
404ba9      1
404ba9      2
e04f90      1
e04f90      3

Tags

tagId       tagText
1           document
2           personal
3           work

Expected Result

If a user searched for both personal and document as defined in tags, return 404ba9 because both (404ba9, 1) and (404ba9,2) exist in taggings.

select sqlite sql
1 Answers

I'd join the tables, have a condition on the tags, and count the number of matches:

SELECT   fileHash
FROM     taggings 
JOIN     tags ON taggings.tagId = tags.tagId
WHERE    tagText IN ('text1', 'text2')
GROUP BY fileHash
HAVING   COUNT(*) = 2
user_24227760
Related