SELECT 1 FROM JOIN with complex conditions

user_1264589 0
Viewed 81

I have two tables with a one-to-many relationship, like so:

CREATE TABLE samples (                                                
     id BIGSERIAL,                                                                            
     name character varying(250)  NOT NULL,                                   
     create_date timestamp without time zone DEFAULT NOW(),                           
     PRIMARY KEY (id),
);

CREATE TABLE sample_compounds (                                       
     id BIGSERIAL,
     sample_id integer ,                                                               
     compound_name character varying(250)  NULL,                              
     is_mixture_analysis boolean NULL,                                       
     score double precision  NULL,                                            
     PRIMARY KEY (id),                                                        
     CONSTRAINT sample_compounds_sample_id_fkey                                   
        FOREIGN KEY (sample_id) 
        REFERENCES samples(id)
);

I need to join the two tables and select one row from sample_compounds based on certain conditions. In pseudo SQL, I'd imagine it would be something like this, but the multiple conditions has me confused about where to start.

SELECT s.id, s.name, s.create_date, sc.compound_name
FROM samples s
LEFT JOIN LATERAL (
  SELECT id, sample_id, compound_name, score, is_mixture_analysis
  FROM sample_compounds
  WHERE 
   condition1 
   OR condition2 
   OR condition3 
  LIMIT 1
) sc ON sc.sample_id=s.id

Here are the conditions I need to have...

Condition 1: If is_mixture_analysis has true values only, select the one with the highest score. For example, if the joining data from sample_compounds looked like this:

                   compound_name                    | score | is_mixture_analysis 
----------------------------------------------------+-------+---------------------
 Soluble Starch                                     |    97 | t
 Starch                                             |    96 | t
 Illite, Morris IL,                                 |    95 | t
 Acacia gum                                         |    94 | t
 Attapalgite, crude, Attapalgus, GA                 |    62 | t

The answer should be: Soluble Starch.

Condition 2: If is_mixture_analysis has false values only, select the one with the highest score. For example, if the joining data from sample_compounds looked like this:

              compound_name               | score | is_mixture_analysis 
------------------------------------------+-------+---------------------
 Nail Polish Remover (Methyl Acetate)     |    96 | f
 4-Styrenesulfonic acid                   |    95 | f
 Dowex                                    |    94 | f
 1,6-bis-(p-Methyl phenyl)-1,6-hexanediol |    94 | f

The answer should be: Nail Polish Remover (Methyl Acetate).

Condition 3: If is_mixture_analysis has both true and false values, select the row with the highest score where is_mixture_analsis is false but only if it has a score >= 95, else select the row with the highest score where is_mixture_analysis is true. For example, if the joining data from sample_compounds looked like this:

            compound_name             | score | is_mixture_analysis 
--------------------------------------+-------+---------------------
 Adenosine triphosphate disodium salt |    98 | f
 Stannous sulfate                     |    97 | f
 Vanadyl sulfate                      |    96 | f
 Cerium IV sulfate                    |   100 | t

The answer should be: Adenosine triphosphate disodium salt.

But, if the data looked like this:

            compound_name             | score | is_mixture_analysis 
--------------------------------------+-------+---------------------
 Adenosine triphosphate disodium salt |    94 | f
 Stannous sulfate                     |    93 | f
 Vanadyl sulfate                      |    92 | f
 Cerium IV sulfate                    |   100 | t
 Cerium sulfate                       |   99  | t

The answer should be: Cerium IV sulfate.

Any help on this would be greatly appreciated!

postgresql
2 Answers

here is how you can do it using window functions

select s.id, s.name, s.create_date, sc.compound_name, sc.score
from samples s
join sample_compounds sc
on s.id = sc.sample_id 
join ( select
         sample_id
         ,is_mixture_analysis
         ,max(score) score 
         ,row_number() over (partition by sample_id order by case when is_mixture_analysis= false and max(score) >= 95 then 1 else 0 end desc) rn 
       from sample_compounds
       group by sample_id ,is_mixture_analysis
) t
 on t.sample_id = sc.sample_id 
 and t.is_mixture_analysis = sc.is_mixture_analysis
 and t.score = sc.score
 and t.rn =1 ;

so basically I'm grouping by sample_id ,is_mixture_analysis to find out the max score but also using row_number() to check if there are both true/false in is_mixture_analysis in a sample , to sort based on the score >= 95 and is_mixture_analysis = false.

user_13674540

One way to accomplish this is to use CTEs to get a count of all possible samples, a count of those with true values, and a count of those with false values and then for your criteria see if the couts match up:

WITH
-- count of total sample compounds per sample
numentries AS
(SELECT sample_id , count(id) c
FROM sample_compounds
WHERE is_mixture_analysis = true
GROUP BY sample_id), 
-- count of true sample compounds per sample
numtrue AS
(SELECT sample_id , count(id) c
FROM sample_compounds
WHERE is_mixture_analysis = true
GROUP BY sample_id),
-- count of false sample compounds per sample
numfalse AS
(SELECT sample_id , count(id) c
FROM sample_compounds
WHERE is_mixture_analysis = false
GROUP BY sample_id)
--core query
SELECT s.id, s.name, s.create_date, sc.compound_name
FROM samples s
 LEFT OUTER JOIN numentries ne ON (s.sample_id = ne.sample_id)
 LEFT OUTER JOIN numtrue nt ON (s.sample_id = ne.sample_id)
 LEFT OUTER JOIN numfalse nf ON (s.sample_id = ne.sample_id),
sample_compounds sc
  WHERE sc.sample_id=s.id
  AND (
   --contiditon 1 - all true
   (ne.c = nt.c)  
   --condition 2 - all false
   OR ((ne.c <> nt.c) AND (nf.c = nt.c) )
   --condition 3 - mix of true and false and score greater than 95
   OR ((ne.c <> nt.c) AND (nf.c <> nt.c) AND (sc.score > 95))
   )   
ORDER BY sc.score DESC
LIMIT 1

There is probably a more elegant way to do the CTEs in a single CTE.

user_157506550
Related