SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2002
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting members with 0 posts and 0 comments in my blog

    I really need help with a counting query, as my sql is a bit rusty...

    I have a user table with thousands of users, most of which are spam signups that I want to get rid of. So I have decided that I will delete all users that have been registered for at least 3 months and have not posted or commented on anything in the blog. Users can have posts, since the blog accepts submissions, and you don't need to be a registered user to post comments either..

    I have roughly these tables:

    USERS
    -------
    id,
    email,
    signup_date

    POSTS
    -------
    post_id,
    user_id (FK),
    post_datetime

    COMMENTS
    -------
    comment_id,
    author_email,
    comment_datetime

    So I need to query and find out:
    Which users have signed up more than 3 months ago and have not yet commented any posts nor submitted any posts of their own on the blog?

    Anyone got a hint?
    Torkil Johnsen
    kampforum.no

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    delete
      from users
     where signup_date 
             < date_sub(current_date, interval 3 month)
       and not exists
           ( select * from posts where user_id = users.id )
    caution: untested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2002
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I could possibly also expand that to include the comments, like this perhaps?

    Code:
    delete
        from users
    where 
        signup_date < date_sub(current_date, interval 3 month)
    and not exists
        ( select * from posts where user_id = users.id )
    and not exists
        ( select * from comments where author_email = users.email)
    Torkil Johnsen
    kampforum.no

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes you could
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •