Posted by: defnop552
Also, Daza. Wouldn't it be better to search by member ID numbers?
where p.uid = (select uid from users where memberID = '8013399');Assuming you knew it, sure, but memberIDs don't really mean anything.
One of the other problems I've realised is that if you look back at some of the older posts you'll notice some deleted users' usernames are the same as those that currently exist. That is to say, there can be multiple users with the same username, just not "active" (or "non-deleted"), which rules out the possibility of the username being a primary key. So with that in mind, selecting the uid based on the username could potentially return more than one row, which would break that query.
A better query (assuming the site continues to store "deleted" users) would be to make the subquery something like SELECT uid FROM Users WHERE username = 'dazarobbo' AND deleted = 0, which would return only one row since you can't sign up a new account with the same username as an existing one (a non-deleted one).
Of course, maybe this is wrong and the posts table (assuming there is one) actually stores the literal username of user making the post instead of referring to it with a foreign key back to the users table (again, assuming that exists too).
Though that wouldn't account for when we've seen someone's username changed, because when it is, all usernames attached to the posts that user has made also change, which would more likely point to there being a foreign key for the user in the posts table, probably referring back to the memberID/uID for the user. While that could be the case, it could also be as simple as running an UPDATE to change each username on a post as well, hence no referencing at all.
The last thing to figure out would be what exactly the primary key is for each user. It could be a number of things, but it could be a combination of things, which makes it difficult.
Given the naming of memberID and uID, it would likely point to either of them, or the combination of them, but it's far too difficult to tell, especially with the way they seem to be used interchangeably to refer to the same profile (eg. username link vs. groups link). So I'll just go with what Achronos says.
Trying to infer things about how the site is structured (especially on the backend) is a really interesting challenge ;).