Monday, 9 September 2013

Joining SELECT variable into different table columns ( multiple tables in one query )

Joining SELECT variable into different table columns ( multiple tables in
one query )

I'm trying to join the result from one specific SELECT variable into a a
different colum from another table.
user_friends table:
+-------------------------------------------+
| friend_id | friend_one | friend_two |
+-------------------------------------------+
users table:
+------------------+
| uiD | username |
+------------------+
The following query retrieves random friends that my followers are
following and suggests them to me, currently it's only retriving their
uiD(id). I'm unsure of how to join these two tables together and get the
GROUP BY possible_friend below to connect to the users table.
$sth = $this->db->prepare("
SELECT friend_two AS possible_friend
FROM user_friends
WHERE friend_one IN (SELECT friend_two
FROM user_friends WHERE friend_one = :uiD)
AND friend_two NOT IN (SELECT friend_two
FROM user_friends WHERE friend_one = :uiD)
AND NOT friend_two = :uiD
GROUP BY possible_friend
ORDER BY RAND()
");
$sth->execute(array(':uiD' => $uiD));
$data = $sth->fetch();
return $data;
As an example, I'm user 10.
uiD = 10 : Username : Jonathan
uiD = 20 : Username : Gabriel
uiD = 30 : Username : Lisa
uiD = 40 : Username : Emily
I'm logged in as Jonathan, that is following Gabriel, but Jonathan is not
following Lisa nor Emily. Then it should show on a div.
Follow Suggestions: Emily or Lisa
With the query above, I'm just able to get their ID's randomly but I'm
unable to join the Username from the users table with the possible_friend
from the user_friends table.
I've tried the following.
$sth = $this->db->prepare("
SELECT F.friend_two AS possible_friend,
U.username
FROM user_friends F, users U
WHERE F.friend_one IN (SELECT friend_two
FROM user_friends WHERE friend_one = :uiD)
AND F.friend_two NOT IN (SELECT friend_two
FROM user_friends WHERE friend_one = :uiD)
AND NOT F.friend_two = :uiD
GROUP BY possible_friend
ORDER BY RAND()
");
$sth->execute(array(':uiD' => $uiD));
$data = $sth->fetch();
return $data;
but it just returns another username, since I'm unable to connect the
U.username with the possible_friend.

No comments:

Post a Comment