I've been fighting this one for a while, and would love some help. I'm sure there's a simple answer. Basically, I have two tables whose data I need to group together. STATES is simply a list of states with STATEID and STATENAME columns, and DOCSTATES contains the STATEID and DOCUMENTID column. I need to group these together to identify which STATEID values exist in DOCSTATES so I can provide a checked checkbox on my page. The query:

This works just fine. I get a list of all of the STATEIDs, and the matching STATECOUNT values are all '2' instead of '1'. So in my ASP code, I'm set. The only thing missing is the STATENAME, but whenever I try to include it in the top query, I get that annoying message about having to include it in the GROUP BY clause. When I add STATENAME to the GROUP BY clause, it "de-aggregates" the COUNT column, and I lose the '2' values.

I hope that makes sense. I feel like I'm really close, just can't seem to fit that STATENAME column in.

SELECT s.StateID,
ISNULL(ds.StateCount, 0) AS StateCount
FROM dbo.States AS s
LEFT JOIN (
SELECT StateID,
COUNT(*) AS StateCount
FROM dbo.DocStates
WHERE DocumentID = '43'
GROUP BY StateID
) AS ds ON ds.StateID = s.StateID;