MySql中的多个内部联接无法正常工作

Question

I have 3 tables :

useractivitylog

id   userid   activity_type  activity_id   date

which will store all user activities.

requestfriend

opid  userid  friendid  requesttime  message  source  requesttype  friendemail  status

which will store friends details. userid and friendid can be the user's id. If user A has send friend's request to user B, then userid will contain the id of A and friendid will be the id of B. So anyway B is the friend of A and A is the friend of B.

auth_user_profiles

id  user_id  first_name  last_name  profileimage

which stores user profile information.

What I need is to select all my friends activities from useractivitylog table. I tried with the following query. But it didnt worked.

    echo "select ua.*,rf.opid,aup.user_id,aup.first_name,aup.last_name,
        aup.profileimage from useractivitylog as ua INNER JOIN requestfriend as rf 
   INNER JOIN auth_user_profiles as aup on
   aup.user_id= IF (rf.userid='$userid',rf.friendid,rf.userid) 
   WHERE (rf.userid=$userid or rf.friendid=$userid)
   and rf.status='2' and ua.userid!=$userid";

Here, $userid is my user id. Actually what I want is to retrieve the profile information and the activities of users who are friends of mine. But know, it is returning all rows in useractivitylog table even if the userid is not in my friends list.

Can anyone help me to find an appropriate query for this. Thanks in advance.

Answer 1

You don't join properly. The only condition for a useractivitylog being linked to the other tables is that its user ID is not $userid. That is true for most records in the table, so you almost get a cartesian product (cross join).

BTW: You don't join auth_user_profiles properly either, because you compare with the string '$userid' instead of comparing with $userid.

Let's re-write the statement such that we retrieve the friends first and join the other tables with these:

select 
  ua.*,
  friend.opid,
  aup.user_id,
  aup.first_name,
  aup.last_name,
  aup.profileimage 
from 
(
  select distinct
    case when userid = $userid then friendid else userid end as id,
    opid
  from requestfriend
  where $userid in (userid, friendid) and status = '2'
) as friend
inner join useractivitylog as ua on ua.userid = friend.id
inner join auth_user_profiles as aup on aup.user_id = friend.id;