I am not skilled in SQL , and I've spent a lot of time writing non-executable SQL
I almost give up...
Can anybody write some SQL code to grab the old/inactive users that fulfill aLL of these criterias :
- ubbt_USER_PROFILE.USER_TOTAL_POSTS = 0
- ubbt_USERS.USER_REGISTERED_ON prior to 2007/1/1
- ubbt_USER_DATA.USER_LAST_VISIT_TIME prior to 2007/1/1
- User never wrote a message : USER_ID should not in ubbt_PRIVATE_MESSAGE_TOPICS.USER_ID . This line is very slow , I don't know how to speed it up.
- User with zero or only one PM (from Admin's welcome message , admin USER_ID = 2 ) I cannot write this one , either
select USER_ID ,
USER_LOGIN_NAME ,
USER_DISPLAY_NAME ,
USER_REGISTRATION_EMAIL ,
FROM_UNIXTIME(USER_REGISTERED_ON) as regTime ,
FROM_UNIXTIME(ubbt_USER_DATA.USER_LAST_VISIT_TIME) as lastTime
from ubbt_USERS
join ubbt_USER_PROFILE using (USER_ID)
join ubbt_USER_DATA using (USER_ID)
where
USER_REGISTERED_ON < UNIX_TIMESTAMP('2007-01-01 00:00:00')
and ubbt_USER_DATA.USER_LAST_VISIT_TIME < UNIX_TIMESTAMP('2007-01-01 00:00:00')
and ubbt_USER_PROFILE.USER_TOTAL_POSTS = 0
and USER_ID not in (select distinct USER_ID from ubbt_PRIVATE_MESSAGE_TOPICS)
group by USER_ID
order by USER_ID;
This is what I could achieve , it doesn't find out "users with zero or only one PM from Admin"
It would be better if it can be accomplished in one SELECT command , so that I can insert the code to membersearch.php .
Thanks a lot !!
