I have an elementary query that gets a list of players. The players table has player_id, player_name, player_location amongst others.

Here are a few other working queries used on the site.

Get player ratings from the player_ratings table:
SELECT AVG(stars) as rating, COUNT(*) as count
        FROM player_ratings
        WHERE player_id = '$player_id';
Get player ladder wins and losses from games_ladder table:
SELECT s.player_name AS winner, r.player_name AS loser,
s.player_id AS winner_id, r.player_id AS loser_id, g.date
                FROM (
                        SELECT * FROM games_ladder
                        WHERE (winner_id = $player_id)
                        OR (loser_id = $player_id)
                ) AS g
                JOIN players AS s ON g.winner_id = s.player_id
                JOIN players AS r ON g.loser_id = r.player_id
                ORDER BY g.date DESC;
Get player league wins and losses from the games table:
        challenger.player_name AS challenger,
        challengee.player_name AS challengee,
        FROM (
                SELECT * FROM games
                WHERE (challenger_id = $player_id)
                OR (challengee_id = $player_id)
        ) AS g
        JOIN players challenger ON challenger.player_id = g.challenger_id
        JOIN players challengee ON challengee.player_id = g.challengee_id
        ORDER BY g.date DESC;
So, as it stands I run these queries separately and combine the results.

What I have been trying to achieve - with absolutely no luck I might add - is a query that gets all players, their ratings, their ladder wins and losses, and their league wins and losses.

I also believe the games_ladder and games tables could be better queried with a COUNT() but have had zero luck with this!

Pseudo code:
SELECT player_id, player_name, rating, ladder_wins, ladders_losses, league_wins, league_losses
FROM players
JOIN WITH player_ratings QUERY
JOIN WITH games_ladder QUERY
JOIN WITH games_league QUERY
WHERE *various possible queries:* player_name LIKE, or player_location = etc
Any suggestions would be appreciated...