mysql random field (condition, multiple table, compare)
Assume i have two tables. "accounts" and "accounts_logs"
"accounts" (accid, type)
"accounts_log" (accid, ip, timestamp)
Here i want to choose a radom 3 accids from "accounts" if available, but each of them should be compared with "accounts_log".
Lests say a one of the accid chose is A1, then take users IP and current time - time(). now compare A1 exists in "accounts_log" with the ip, this can exist multiple times but pick one with latest timestamp. Compare the difference in hours in the timestamp and current time, if its less than 12 hours then choose a different accid from "accounts" which goes through the same check, and while taking new accid from "accounts" or if it is greater than 12 hours continue to be A1, this is same even if there is no entries of A1 in "accounts_log".
I hope my explanation was enough. I been trying for weeks and yet can't seem to find a solution.
I'll summarize the steps
1) have to take 3 random `accid` from `accounts` with `type`='normal'
2) while taking, check each `accid` from `accounts` exists in `accid` feild of table `account_logs` with number '123' in `ip` field of `accounts_log`.
3) if exists compare the latest `timespamp` field of `accounts_log` associated with `ip`='123' to current time to see if it is greater than 12 hours.
4) return available entries.