Search code examples
mysqlinner-joinhaving-clause

SELECT with INNER JOIN to count delta and filter results

I'm trying to select all rows from table B with same id and calculate delta for count value from table A and number of rows selected from B like this:

SELECT A.id, 
       A.count, 
       count(*) AS counter, 
       CAST(count(*) AS SIGNED) - CAST(A.count AS SIGNED) AS delta 
FROM table_A AS A 
INNER JOIN table_B AS B ON B.id = A.id 
GROUP BY A.id

Now i have delta calculated but can't filter results where delta > 0 I've tried to put HAVING delta > 0 and WHERE delta > 0 here and there, but no luck. Looks like i'm missing something with scopes in my query.

Solution

  • Is this what you want?

    SELECT
    a.id, 
    a.count, 
    COUNT(*) AS counter
FROM cdb_A a
INNER JOIN db_B AS B
    ON b.id = a.id
GROUP BY
    a.id
HAVING
    COUNT(*) > a.count;