Count how many records have at least one corresponding entry in junction table

[thebigthing313]

Hi,



I'm trying to write a query that counts the records associated with a combination of the fields Inspector and Date, but I only want it to count records that have at least one corresponding entry in another table (a junction table) linked by InspectionID. Right now, I've written this:

SELECT tbl1.InspectorID, tbl1.InspectionDate, Count(tbl1.InspectionID) AS NumberOfInspectionIDs

FROM tbl1 LEFT JOIN tbl2 ON tbl1.InspectionID = tbl2.InspectionID

GROUP BY tbl1.InspectorID, tbl1.InspectionDate

HAVING Count(tbl2.InspectionID) > 0;

But the query is returning numbers that I know can't be right. It seems to be totaling the count of records in tbl1, plus the number of records in tbl2 associated by the link.

What am I doing wrong?

[JoeM]

If you want records that only exist in both tables, why are you doing a LEFT join? You should be doing an INNER join instead.

[lfpm062010]

Two things stand out.

1) You are using the LEFT JOIN. It will bring every records in tbl1 back and fill the tbl2 column with NULL values.
2) Count will not count NULL values.

So, you can try one of the two things that might fix your issue with the information given.

1) Using "INNER JOIN". It will only bring back the record that tbl1 matches tbl2.
2) Count the tbl2 "InspectionID" instead of tbl1. It will only count the not NULL value.

Hope this will get your answer.

[thebigthing313]

Well don't I feel silly... Thank you guys for the help. The INNER JOIN + counting from tbl2 instead of tbl1 was my solution.

All this learning of things is making me think too convolutedly when solutions can be much more simple lol

Thanks again for the assistance.

[thebigthing313]

Just in case anyone might be interested: So I thought the solution above worked, but it actually was only the right count if each record only had 1 corresponding entry in the junction table.

I had to use a subquery using SELECT DISTINCT. But thanks anyway for pointing me in the right direction.