CODEWITHSUNDEEP
phpmysqlsql
Dylan Cross
Dylan Cross

Reputation: 5986

MYSQL combine two queries to get the functionality

I have a query that retrieves the users that are online, and a users friends. Now I want to know the best way to combine the two so I can get the results of the users friends that are online.

Friends query:

 SELECT 
       CASE WHEN userID=$session 
       THEN userID2 
       ELSE userID 
       END AS friendID 
 FROM friends 
 WHERE userID=$id OR userID2=$session
 LIMIT 18

users online:

SELECT * 
FROM usersActivity 
WHERE setActivity!=3 
AND userID!=$session

usersActivity.userID needs to match friendID

Upvotes: 0

Views: 176

Answers (2)

Vyktor
Vyktor

Reputation: 21007

Query should be: 

SELECT users.name
FROM usersActivity
INNER JOIN friends ON
    (usersActivity.userID = usersActivity.userID AND usersActivity.userID2 = $session) OR
    (usersActivity.userID2 = usersActivity.userID AND usersActivity.userID = $session)
INNER JOIN users ON
    (usersActivity.userID = users.userID) OR
    (usersActivity.userID2 = users.userID)
WHERE usersActivity.setActivity!=3 
    AND usersActivity.userID!=$session
    AND users.userID != $session
GROUP BY users.id

You may use COUNT(user.id) if you want only count of users. Or select all names (store them for later use in listing) and use only mysql_num_rows() for getting actual number of friends online

Upvotes: 1

iLLin
iLLin

Reputation: 760

I think I understand what your after:

SELECT userID FROM usersActivity 
  WHERE setActivity !=3 
  AND userID IN(
  (SELECT userID FROM friends WHERE userID2=$id)
);

This assumes you have double rows for your friend linking table and $id is the current logged in user.

userID  userID2
1       2
2       1

Using subqueries in your where statement should consolidate this. Not sure if this will be faster or not, depends on how you are doing things so profile it. You can join on your users table to get the friends name information and what other info you need.

Upvotes: 0

Related Questions