php mysql使用pdo在同一个表上连接查询

Question

This is my table in which ID is table's primary key and SUPER_ID is used to manage hierarchy for example here, A is super user of B and B is super user of C while A is super-super user of C............ ID AND SUPER_ID MAY NOT NECESSARY BE SEQUENTIAL

Now question is when A is logged in, He can see details of his own and of Both B and C While B is logged in He can see details of His own and C only. And C can see his own details only.

I have written this query:

<?php
$sql="SELECT * from TABLE 
WHERE 
ID =:loginId OR                                                  
ID IN 
(
SELECT ID FROM TABLE 
WHERE SUPER_ID =:SuperId
)";
$stmt = $conn->prepare($sql);
$stmt->execute(array(':logedIn' => $_SESSION['sess_login_id'] , ':SuperId' => $_SESSION['sess_login_id']) );
?>

This query gives me results of A and B when i logged in as User A. What query should I write so that I can get results of A , B and C when I logged in as User A? Because A is a super user of B and super-super user of C. Please help. Thank You.

Answer 1

If you have only three hierarchy levels, you can do it like this:

SELECT DISTINCT u.*
FROM user loggedin
LEFT JOIN user children      ON children.SUPR_ID      = loggedin.ID
LEFT JOIN user grandchildren ON grandchildren.SUPR_ID = children.ID
JOIN user u ON u.ID IN (loggedin.ID, children.ID, grandchildren.ID)
WHERE loggedin.ID = :loginId

http://sqlfiddle.com/#!9/3d93c/7