Help with getting users and unique comments per widget

Databases
1

Given the three tables:

WIDGETS
[table=“width: 200, class: outer_border”]
[tr]
[td]id[/td]
[td]name[/td]
[/tr]
[tr]
[td]1[/td]
[td]ABC Widget[/td]
[/tr]
[tr]
[td]2[/td]
[td]XYZ Widget[/td]
[/tr]
[tr]
[td]3[/td]
[td]123 Widget[/td]
[/tr]
[tr]
[td]4[/td]
[td]789 Widget[/td]
[/tr]
[/table]

USERS
[table=“width: 200, class: outer_border”]
[tr]
[td]id[/td]
[td]name[/td]
[/tr]
[tr]
[td]1[/td]
[td]John Doe[/td]
[/tr]
[tr]
[td]2[/td]
[td]Jane Doe[/td]
[/tr]
[tr]
[td]3[/td]
[td]Mr. Rogers[/td]
[/tr]
[/table]

WIDGET_COMMENTS
[table=“width: 200, class: outer_border”]
[tr]
[td]widget_id[/td]
[td]user_id[/td]
[td]comment[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]testing[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]a test[/td]
[/tr]
[tr]
[td]2[/td]
[td]2[/td]
[td]hello![/td]
[/tr]
[tr]
[td]3[/td]
[td]1[/td]
[td]sample text[/td]
[/tr]
[tr]
[td]4[/td]
[td]2[/td]
[td]woohoo[/td]
[/tr]
[tr]
[td]4[/td]
[td]2[/td]
[td]goodbye![/td]
[/tr]
[/table]

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

[table=“width: 300, class: outer_border”]
[tr]
[td]users.name[/td]
[td]# widgets[/td]
[/tr]
[tr]
[td]John Doe[/td]
[td]2[/td]
[/tr]
[tr]
[td]Jane Doe[/td]
[td]1[/td]
[/tr]
[/table]

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?

2

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.

3

this query requires a GROUP BY clause to work correctly

4

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