Search code examples
sqlpostgresqlrelational-division

Selecting where keywords match both values but in different rows

I am trying to return what id contains both keywords. For example my data is:

|fID|keyword|
|1  |word1  |
|1  |word2  |
|2  |word1  |
|3  |word2  |

if I do the SQL SELECT fID FROM table WHERE keyword = 'word1' AND keyword = 'word2';

it returns with 0 results matching I assume because it wants them to be in the same row when all I am wanting is if they both are connected with the same fID.

If I do OR instead of AND it shows the fIDs that dont have both in.

I would expect the result to output fID 1. I have been messing around with brakets in various places and group by but cannot get any success in this.

Solution

  • Untested, but you could try something like this:

    SELECT fID 
FROM table 
WHERE keyword = 'word1' OR keyword = 'word2'
GROUP BY fID
HAVING COUNT(DISTINCT keyword) = 2