-1

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

viruskimera
  • 193
  • 16
  • The "best way" depends on missing details and requirements: Postgres version, table definition showing data types and constraints (`CREATE TABLE` statements), min, max, avg number of given patterns. Basically, "relational division" is the keyword here, and there are *many* possible solutions. – Erwin Brandstetter Aug 16 '19 at 15:58

2 Answers2

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon, seems working! Still Testing it... I just had to left join your query to the TablePatterns to actually have patternName in (?, ?) and I will be counting and passing the number of patternNames the User is filtering on, I´m assembling the query from Java – viruskimera Aug 16 '19 at 15:59
1

This is a case of . For exactly two given "patterns" like in your example, and based on some assumptions, this should be fastest:

SELECT resultid
FROM   resultpatterns t1
JOIN   resultpatterns t2 USING (resultid)
WHERE  t1.pattern = (SELECT pattern FROM patterns WHERE name = 'Name2')
AND    t2.pattern = (SELECT pattern FROM patterns WHERE name = 'Name3')

Assumptions:

  • You only need resultid in the SELECT list.
  • You have have necessary indexes in place, so we don't descend into a sequential scan.
  • You know the number of given patterns.
  • A standard m:n implementation as outlined here:
  • patterns.name is defined UNIQUE.

The "best way" depends on missing requirements. There are many possible ways:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Hi Erwin thanks for the answer, basically I want to know if a ResultId has all Given PatternNames, Indeed I get the ResultId as outer query column, table1->TableResults relates to table2-> TableResultsPatterns (ResultId) and table2 has the PatternIds from table3 ->TablePatterns and the PatternNames that I need to used to filter on are in that 3rd table – viruskimera Aug 16 '19 at 16:30
  • 2
    @viruskimera: One way would be to resolve *names* to *patterns* with a subquery - as added above. Please address my requests if you wish to continue here. Any advice must be based on actual `CREATE TABLE` statements, indexes and requirements - and possibly more data. – Erwin Brandstetter Aug 16 '19 at 16:38