Tag Schema select intersection I have a, I think pretty common, schema:

samples &lt;-> sample_labels &lt;-> labels

Here's an SQL Fiddle. I would like to achieve a selection of all samples that have all of the tags specified.

Given parameters of:


label.key=narf, label.value=zord
label.key=foo, label.value=bar


I only would like to retrieve samples that are having both of these labels. A where of

-- will find nothing cause conditions can't be satisfied
WHERE
  label.key = 'narf' AND label.value = 'zord'
  AND label.key = 'foo' AND label.value = 'bar'


will yield 0 rows (seems clear enough because no label tuple can actually fullfill these conditions. Given this:

-- will find either of "narf=zord", "foo=bar"
WHERE
  label.key = 'narf' AND label.value = 'zord'
  OR label.key = 'foo' AND label.value = 'bar'


Will yield all rows in the SQL Fiddle example because (again, seems clear enough to me) every label in that case will fulfill the conditions.

Even worse is this:

-- will also find "foo=zord" or "bar=narf" -&gt; unwanted
WHERE
  (l.key IN ('foo', 'narf')) AND
  (l.value IN ('bar', 'zord'))


My brain is in meltdown mode and I'm pretty close to filtering thru the results in my code. It seems obvious that I'm simpy not smart enough to get the query correct.  :(

What is the query to get only samples that are correlated to ALL the given labels?
You must ensure that both combinations exists.


-- I'd like all samples that have labels of &quot;narf=zord&quot; AND &quot;foo=bar&quot;
-- AND l.key = 'foo' AND l.value = 'bar'
WITH pairs AS
(
    SELECT s.id AS sample_id, s.prefix AS sample, l.id AS label_id, l.key, l.value
    FROM   samples s 
    JOIN   sample_labels sl ON (s.id = sl.sample_id)
    JOIN labels l ON (sl.label_id = l.id)
)
SELECT p.sample_id, p.sample, p.label_id, p.key, value
FROM   pairs p
WHERE  EXISTS (SELECT 1 
               FROM   pairs 
               WHERE sample_id = p.sample_id and key = 'narf' AND value = 'zord')
AND    EXISTS (SELECT 1 
               FROM   pairs 
               WHERE sample_id = p.sample_id and key = 'foo' AND value = 'bar');


sample_id | sample              | label_id | key  | value
--------: | :------------------ | -------: | :--- | :----
        1 | narf=zord &amp; foo=bar |        1 | narf | zord 
        1 | narf=zord &amp; foo=bar |        2 | foo  | bar  


dbfiddle here