mysql query join tables

[shortysbest]

I'm having trouble getting this to work:

SELECT * 
FROM usersActivity 

(JOIN friends ON  usersActivity.userID=friendID
   CASE WHEN friends.userID=$session 
   THEN friends.userID2 
   ELSE friends.userID 
   END AS friendID 

   WHERE (friends.userID=friendID AND friends.userID2=$session) 
   OR (friends.userID=$session AND friends.userID2=friendID)
)
WHERE usersActivity.setActivity!=3 
AND usersActivity.userID!=$session
ORDER BY usersActivity.setActivity ASC

 

I want to return who's online, based on who you're friends with. Currently I have to do this to get it to do what I want:

 

$onlineUsers = mysql_query("
SELECT *
FROM usersActivity 
WHERE setActivity!=3 
AND userID!=$session
ORDER BY setActivity ASC
");

while($users = mysql_fetch_array($onlineUsers))
{
$friendID = $users['userID'];
$friend = mysql_fetch_assoc(mysql_query("
   SELECT 
   CASE WHEN userID=$session 
   THEN userID2 
   ELSE userID 
   END AS friendID 
   FROM friends 
   WHERE (userID=$friendID AND userID2=$session) 
   OR (userID=$session AND userID2=$friendID)"));

   if($friendID==$friend['friendID'])
   {
          print $friendID; //userID that is online
       }      
}

[fenway]

What do you think that case statement is doing?

[shortysbest]

The way my friends table is it has one row for each friendship, there is no specific column for a user, it just depends on who adds who. So this just takes and whenever one row matches the users id, it sets the friend id as the other column.

[kickstart]

Hi

 

That case statement makes no sense there, and also even if coded to make sense is not a nice way of doing things.

 

I would use a UNION of 2 selects, one to check each user field.

 

If you find that messy to look at then you can hide the UNION in a view to return each row twice, once for each way around the user fields are.

 

All the best

 

Keith