SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with getting users and unique comments per widget

    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?
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    First write a query to find widgets with only one comment:

    Code:
    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..

    Code:
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    First write a query to find widgets with only one comment:

    Code:
    SELECT
    WIDGET_ID
    FROM
    WIDGET_COMMENTS
    HAVING
    COUNT(WIDGET_ID) = 1
    this query requires a GROUP BY clause to work correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    this query requires a GROUP BY clause to work correctly
    That's what I get for doing it on the fly...

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •