SQL: Select only if multiple values are IN 1 column from CatalogTable

Question

I would really appreciate if you could help me with the following query; Having the following tables:

----------
**TableResults**
ResultId1
ResultId2
----------

---------------------
**TableResultsPatterns**
ResultId1  pattern1
ResultId1  pattern2
ResultId1  pattern4
ResultId2  pattern3
---------------------

---------------------
**TablePatterns**
pattern1 Name1
pattern2 Name2
pattern3 Name3
pattern4 Name4

---------------------

What is the best way to check if list of values (patternNames from User) are IN or exist in the list of patterns of a particular Result

For example select only the Results that have pattern Names(Name2, Name3)? I have something like:

SELECT***
JOINs***
WHERE***
and exists(select TablePatterns from TableResultsPatterns left join
    TablePatterns f on TableResultsPatterns.patternId = f.id
    where TableResultsPatterns.ResultsId = ResultIdX and patternName in ('Name2', 'Name4'))
GROUPBY***
***

Edit 1:

----------------
**TableResults**
(ResultId pk)
ResultId1
ResultId2
---------------

--------------------------------------------------------
**TableResultsPatterns**
(ResultId                    (fk TablePatterns.PatternId)
fk TableResults.ResultId)
ResultId1                     pattern1
ResultId1                     pattern2
ResultId1                     pattern4
ResultId2                     pattern3
--------------------------------------------------------

------------------------------
**TablePatterns**
(PatternId pk)  (PatternName)
pattern1         Name1
pattern2         Name2
pattern3         Name3
pattern4         Name4

------------------------------

in my main query i have:

right join TableResults wsr on wsr.patient_well_sample_id=XXX.id
left join TableResultsPatterns wsrfp on wsr.ResultId=wsrfp.ResultId
left join TablePatterns fp on wsrfp.final_patterns_id=fp.id

note: I´m string_agg(the PAtternNAmes for every TableResult) in select

Answer 1

If you have a list of patterns and you want the results that contain them, you can use aggregation. For instance:

select resultid
from resultpatterns rp
where pattern in (?, ?, ?)
group by resultid
having count(distinct pattern) = 3;  -- 3 is the size of the list