Search code examples
mysqljoingroup-byhaving

MySQL Getting filtered row from one table based on a value in another table

I am having an issue joining these two tables. The query below will return ID-A 1 and 8. However, I also want to check if ID-A has StatusCode= 2 in TableA. How do I join the two tables that return ID-A 8 only.

TableA

ID-A,  StatusCode
1      1
5      9
8      2

TableB

ID-B  ID-A  ACode
1     1     1
2     1     0
3     5     1
4     5     1
6     5     0
7     5     1
8     8     1
9     8     1



SELECT ID-A FROM TableB WHERE Acode=1 GROUP BY ID-A HAVING COUNT(*) < 3
Solution

  • Join your subquery with TableA.

    SELECT t1.`ID-A`
FROM (
    SELECT `ID-A` 
    FROM TableB 
    WHERE Acode=1 
    GROUP BY `ID-A` 
    HAVING COUNT(*) < 3
) AS t1
JOIN TableA as t2 ON t1.`ID-A` = t2.`ID-A`
WHERE t2.StatusCode = 2