Problem with Count(*) = 0 in HAVING clause 2

[VickyC]

hi to all - consider a table like the one below:

 [b]

tbl_Val [/b]
ID         Pkg        Val_A       Val_B
 10         1           [b]34[/b]          26
 10         2           12          [b]34[/b]
 10         3           55          92

 20         1           10          88
 20         2           20          99
 20         3           30          77

 30         1           12          [b]76[/b]
 30         2           99          44
 30         3           [b]76[/b]          51

 40   etc...

I have written some SQL to count MATCHES between Val_A and Val_B of the same ID.

SELECT T1.ID, T1.Val_A, T1.Val_B
FROM tbl_Val AS T1 INNER JOIN tbl_Val AS T2 ON (T1.ID = T2.ID) AND (T1.Val_A = T2.Val_B)
GROUP BY T1.ID
HAVING (Count(*)=[Enter MATCH COUNT]);

When I run the Query with the Parameter = 1, I get the expected query output showing that there
are 2 records having 1 MATCH. (ID=10 and ID=30).

When I run the Query with the Parameter = 2, I get the expected query output showing that there
are NO records having 2 MATCHES.

Here's the problem...
When run the Query with the Parameter = 0, the output shows NO records. But, I want it to show
that there is 1 record (namely ID=20) that has 0 matches. To picture this more clearly, imagine that tbl_Val has records for 1000 different IDs. I'd like results that tell me something like this:

for MATCH COUNT = 3, there are 100 records having 3 matches
for MATCH COUNT = 2, there are 50 records having 3 matches
for MATCH COUNT = 1, there are 250 records having 3 matches
for MATCH COUNT = 0, there are 600 records having 3 matches this is the problem


I think the problem must be related to the fact that if there are NO MATCHES as is the case for ID=20, then the GROUP BY returns no records at all for this ID. I had hoped that NZ would help, but it doesn't seem to.


Is their a 'fix'?

Thanks, Vicky

 

[PHV]

What about this ?

SELECT  ID
FROM tbl_Val
GROUP BY ID
HAVING Max(DCount('*',"tbl_Val","ID=" & ID & " AND val_B=" & val_A))=[Enter MATCH COUNT]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thank you PHV. It was neat to see how you used Max(DCount...) in the HAVING clause instead of an Inner Join. But, it seems that a new problem was introduced.

First, I added the following records to tbl_Val above so that I have an ID with 2 matches...

tbl_Val 

ID         Pkg        Val_A       Val_B
 40         1           12          12
 40         2           66          18
 40         3           18          44

So, now ID = 20 has 0 matches, ID = 10 and ID = 30 have 1 match, and ID = 40 has 2 matches.

My original code (with a minor change to the SELECT) gives the correct number of matches when there are more than 0 matches, but doesn't identify ID = 20 as having NO matches...

SELECT T1.ID
FROM tbl_Val AS T1 INNER JOIN tbl_Val AS T2 ON (T1.ID = T2.ID) AND (T1.Val_A = T2.Val_B)
GROUP BY T1.ID
HAVING (Count(*)=[Enter MATCH COUNT]);

Your code correctly shows that ID = 20 has no matches, but it 'claims' that ID 10, 30, and 40 all have 1 match. All match counts seem to be either 0 or 1. I'm not sure where the problem is - it looks like it should work...

SELECT  ID
FROM tbl_Val
GROUP BY ID
HAVING Max(DCount('*',"tbl_Val","ID=" & ID & " AND val_B=" & val_A))=[Enter MATCH COUNT]

Thanks in advance for any thoughts.
Vicky

 

[BigRed1212]

Just something else to consider:

When you say
"When run the Query with the Parameter = 0, the output shows NO records. But, I want it to show
that there is 1 record (namely ID=20) that has 0 matches"

you realize that the reason that record doesn't show is because it is not in the set of records where
(T1.ID = T2.ID) AND (T1.Val_A = T2.Val_B)

The slacker fix would be to use your original query and then UNION it up with a query that selected only those records with no matches.

Like I say, just something to think about.

 

[PHV]

What about this ?

SELECT ID FROM (
SELECT T1.ID,Count(*) AS theCount
FROM tbl_Val T1 INNER JOIN tbl_Val T2 ON T1.ID=T2.ID AND T1.Val_A=T2.Val_B
GROUP BY T1.ID
UNION SELECT ID,0 FROM tbl_Val
) U GROUP BY ID
HAVING Max(theCount)=[Enter MATCH COUNT]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thanks PHV (and also BigRed1212 for the UNION idea). This does the trick!