1

I have two tables:

Table A:                Table B:
id  name               id   a_id   param
1    xxx               1      1      3
2    yyy               2      1      4
                       3      1      5
                       4      2      3
                       5      2      4
                       6      2      9

I need to select from Table A such names where a number of related rows from B with 3 <= param <= 5 is less than 3.

In the case above it should yield only yyy, because it has only 2 related rows from B which
have 3 <= param <= 5. While xxx has 3 related rows with such condition.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Sergey Dylda
  • 399
  • 3
  • 12

2 Answers2

2

You can use the following using a GROUP BY with HAVING:

SELECT A.name 
FROM A LEFT JOIN B ON A.id = B.a_id AND B.param BETWEEN 3 AND 5 
GROUP BY A.name 
HAVING COUNT(*) < 3

In case you need all columns of table A you can use the following using a INNER JOIN with the above SELECT:

SELECT A.* 
FROM A INNER JOIN (
    SELECT A.id 
    FROM A LEFT JOIN B ON A.id = B.a_id AND B.param BETWEEN 3 AND 5 
    GROUP BY A.id 
    HAVING COUNT(*) < 3
) A2 ON A.id = A2.id;

demo: https://www.db-fiddle.com/f/jBCw5G1LvrRC37TLVx3UsB/1

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
0

Hmmm . . . you can use group by and join:

select a.*
from a left join
     (select a_id, count(*) as cnt
      from b
      where param >= 3 and param <= 5
      group by a_id
     ) b
     on b.a_id = a.id
where b.cnt < 3 or b.cnt is null;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786