pdo query to sort the result depending on a common field of two tables

ok this is really tricky and i cant wrap my brains around it, it would be great fi you can help me with it,

i am trying to write a query using pdo that does the below,

• get all the users of a formation including the user from the formation table
• join with the team of each user
• get the players of each team in that formation and calculate their score sum
• sort the result by position (from both formation and users table position field) from first to last (1,2,3,4..)

What i tried in Code

function getFormation(Request $request, Response $response) {
    $id = $request->getAttribute('id');

$sql = "SELECT u.id, u.formation, u.position FROM users AS u INNER JOIN formation AS f ON f.userid = u.id INNER JOIN teams as t ON t.userid = u.id WHERE f.id = :id OR u.formation =:id";

try {
    $db = new db();
    $db = $db->connect();
    $stmt = $db->prepare($sql);

    $stmt->bindParam(":id", $id);

    $stmt->execute();

    $formation = $stmt->fetch(PDO::FETCH_OBJ);

    $db = null;

    if(empty($formation)) {
        $response->getBody()->write
        ('
        {
            "error":
            {
                "status":"400",
                "message":"Invalid Request"
            }
        }');
    } else {
        $response->getBody()->write(json_encode($formation));
    }
} catch(PDOException $e) {
    $response->getBody()->write
    ('
    {
        "error":
        {
            "message":'. $e->getMessage() .'
        }
    }');
}
};

what i cannot make work is

• joining the player table on which id should i join it ? i have 10
  players id in the teams table

• calculating the sum of all players scores

• sort the result by position (from both formation and users table
  position field) from first to last (1,2,3,4..)

result sample / data returned

formation position, formation user, total score 1(p1 score 1 + p2 score 1 + p3 score 1...), total score 2, total score 3, total score 4, total score 5, total score 6, team points

here is my db structure

========================================

formation table

• "id" -> user created formation

• "userid"

• "position" -> user created formation position

========================================

users table

• "id"

• "formation" -> user joined formation

• "position" -> user joined formation position

========================================

teams table

• "id"

• "userid"

• "points"

• "player1"

• "player2"

• "player3"

• "player4"

• "player5"

• "player6"

• "player7"

• "player8"

• "player9"

• "player10"

========================================

players table

• "id"
• "score1"
• "score2"
• "score3"
• "score4"
• "score5"
• "score6"