This may be simple but I can't figure it out...

I have two tables:

tbl_results:

runID | balance |
1     | 3432
2     | 5348
3     | 384 

tbl_phases:

runID_fk | pc |
1        | 34 
1        | 2
1        | 18
2        | 15
2        | 18
2        | 20
3        | -20
3        | 10
3        | 60

I want to get a recordset of: runID, balance, min(pc), max(pc) only where pc>10 and pc<50 for each runID as a group, excluding runIDs where any associated pc value is outside of value range.

I would want the following results from what's described above:

runID | balance | min_pc | max_pc
2     | 5348    | 15     | 20

... because runID=1&3 have pc values that fall outside the numeric range for pc noted above.

Thanks in advance!

CodePudding user response：

You may apply filters based on your requirements in your having clause. You may try the following.

Query #1

SELECT
    r.runID,
    MAX(r.balance) as balance,
    MIN(p.pc) as min_pc,
    MAX(p.pc) as max_pc
FROM
    tbl_results r
INNER JOIN 
    tbl_phases p ON p.runID_fk = r.runID
GROUP BY 
    r.runID
HAVING
    MIN(p.pc)>10 AND MAX(p.pc) < 50;

Query #2

SELECT
    r.runID,
    MAX(r.balance) as balance,
    MIN(p.pc) as min_pc,
    MAX(p.pc) as max_pc
FROM
    tbl_results r
INNER JOIN 
    tbl_phases p ON p.runID_fk = r.runID
GROUP BY 
    r.runID
HAVING
    COUNT(CASE WHEN p.pc <= 10 or p.pc >= 50 THEN 1  END) =0;

View working demo on DB Fiddle

Updated with comments from Rahul Biswas