Count number of not exist in child table

Question

Essentially what I'm trying to do is count the number of rows something doesn't exist in an audit/history table. I'd like the following query to return a count of one per detail. Currently it gives me one per row in the history table.

--Detail Table
ID    DETAIL_GROUP
1     A
2     B
3     B

--Detail History Table
DETAIL_ID_FK    VALUE1
1               NOT_MATCH
1               NOT_MATCH
2               MATCH
2               NOT_MATCH
3               MATCH
3               NOT_MATCH

SELECT D.DETAIL_GROUP, COUNT(*)
FROM DETAIL D
WHERE (NOT EXISTS(
          SELECT NULL
          FROM DETAIL_HISTORY HI 
          WHERE HI.D_ID_FK = D.ID 
          AND HI.VALUE1 = 'MATCH'))
GROUP BY D.DETAIL_GROUP;

I'd like to see the following result:

DETAIL_GROUP    COUNT(*)
A               1

but I'm receiving the following result:

DETAIL_GROUP    COUNT(*)
A               2

Thank you in advance for any assistance provided.

With your sample you provided, I am getting the result `A`, `1` which is what you stated you want -- see this demo -- http://sqlfiddle.com/#!3/013a0/1 — Taryn, May 23 '13 at 17:59
side note, You should avoid correlated subqueries, they're expensive. Use `where D.ID not in (select HI.D_ID_FK from DETAIL_HISTORY HI where HI.VALUE1 = 'MATCH')` — τεκ, May 23 '13 at 18:12
@τεκ There are certain problems with `NOT IN` and nulls. See **[NOT IN vs NOT EXISTS](http://stackoverflow.com/questions/173041/not-in-vs-not-exists/11074428#11074428)** Performance depends on the DBMS. Are you sure that Oracle does not perform well with correlated subqueries? — ypercubeᵀᴹ, May 23 '13 at 19:59
I think it's reasonable to assume that D_ID_FK is not null in this context. I read some blog post about correlated subqueries in Oracle some time ago, though I don't know how good Oracle's optimizer is these days. — τεκ, May 23 '13 at 20:04
Thank you for the responses. I found my issue and it had nothing to do with this query. What should I do with this post? — Pranke, May 24 '13 at 11:09

score 0 · Answer 1 · answered Jul 19 '13 at 11:35

Assuming that your detail table is as follows:

D_ID    VALUE1
1       MATCH
1       NOT_MATCH
2       MATCH
2       NOT_MATCH
3       MATCH
3       NOT_MATCH

The below query:

SELECT d.detail_group, count(*)
FROM detail d
JOIN detail_history dh ON dh.d_id = d.id 
WHERE dh.value1 = 'MATCH'
GROUP BY d.detail_group

Would produce:

DETAIL_GROUP    COUNT(*)
A                   1
B                   2

The above query creates the groups matching the ids and then goes into each group and restricts the items based on value1.

Count number of not exist in child table

1 Answers1