Help with getting users and unique comments per widget

[CreedFeed]

Given the three tables:

WIDGETS

id	name
1	ABC Widget
2	XYZ Widget
3	123 Widget
4	789 Widget

USERS

id	name
1	John Doe
2	Jane Doe
3	Mr. Rogers

WIDGET_COMMENTS

widget_id	user_id	comment
1	1	testing
2	1	a test
2	2	hello!
3	1	sample text
4	2	woohoo
4	2	goodbye!

How could I compile a list of users and the number of widgets they, and only they, entered comments on. Example results:

users.name	# widgets
John Doe	2
Jane Doe	1

Based on the data, widget 1 has 1 comment (by John Doe only), widget 2 has 2 comments (by John Doe and Jane Doe), widget 3 has 1 comment (by Jane Doe), and widget 4 has two comments (by John Doe). Since widget 2 has comments by two different users, it wouldn't be counted in the results. The other widgets only have comments by one specific user, so they would be counted in the results. Also, since Mr. Rogers had no comments, he shouldn't show up in the list.

Any help with this?

[K. Wolfe]

First write a query to find widgets with only one comment:

SELECT
WIDGET_ID
FROM
WIDGET_COMMENTS
HAVING
COUNT(WIDGET_ID) = 1

From here you'll want to bring in the users name from the user table, and count em up again..

SELECT
U.NAME, COUNT(U.NAME) AS NUMBER_OF_WIDGETS
FROM
(SELECT
WIDGET_ID, USER_ID
FROM
WIDGET_COMMENTS
HAVING
COUNT(USER_ID) = 1) AS SCW
LEFT JOIN USERS U ON SCW.USER_ID = U.USER_ID
GROUP BY 
U.NAME

One problem with this approach is that if a user comments on the widget more than once, it will be excluded even if someone else hasn't done so. This can be solved by motifiying the starting query.

[r937]

First write a query to find widgets with only one comment:

SELECT
WIDGET_ID
FROM
WIDGET_COMMENTS
HAVING
COUNT(WIDGET_ID) = 1

this query requires a GROUP BY clause to work correctly

[K. Wolfe]

this query requires a GROUP BY clause to work correctly

That's what I get for doing it on the fly...

SELECT WIDGET_ID
FROM WIDGET_COMMENTS
GROUP BY WIDGET_ID
HAVING Count(WIDGET_ID) = 1