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.
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;