CODEWITHSUNDEEP
phpmysqlsql
uchejava
uchejava

Reputation: 1

How to display users from two MySQL tables

I have two mysql tables-members and addclique. Members table contains the details of all users while addclique table is 'friends' table with two columns-adder_id, which contains id of the user that sent a friend request and clique_id, contains the id of user that accepted the friend request.

Please am trying to select the friends of a particular user but am finding hard getting the details (photo, name, etc) of the friends from the members table because I don't know at which point to join members table to the addclique table. 

$sql = "SELECT i.*, m.* FROM addclique i JOIN members m ON m.id = ******** WHERE adder_id = :id OR clique_id = :id";

$result= $db->query($sql, array('id' => $_SESSION['id']);

Upvotes: 0

Views: 64

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

The query below will select all members that a particular person has added plus all members that have added the same person.

select m.* from members m
join addclique a on m.id = a.adder_id
where a.clique_id = :id

union 

select m.* from members m
join addclique a on m.id = a.clique_id
where a.adder_id = :id

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

If I'm understanding your question correctly, you need to join on m.id = a.adder_id or m.id = a.clique_id. You can do this with in:

set @id := 4;

select *
from members m
    join addclique a on m.id in (a.adder_id, a.clique_id)
where @id in (a.adder_id, a.clique_id)
  and m.id != @id

Upvotes: 1

Related Questions