Q: Complex SQL Union Any SQL Guru's out there I could use some help! I am creating a stored procedure that I believe needs a Union so that all the results are brought back with 1 SELECT statement.
I have simplified my problem to the tables below:
user
user_id      username         name            DOB
------------------------------------------------------
1           JohnSmith1      John Smith     01/01/1990 
2           LisaGreen17     Lisa Green     03/07/1986
3            BarneyB       Barney Brown    09/12/1960
user_team
user_team_id      user_id         team_id       total_score
-------------------------------------------------------------
1                    1               1             29 
2                    2               7             37
3                    3               2             15
private_league
priv_league_id      league_name         host_user     league_password
-------------------------------------------------------------------------
1                   Lisa's League           2           CSUASH429d9 
2                  Barney's Bonanza         3           Jkap89f5I01
user_team_private_league_M2M
id      priv_league_id      user_team_id
----------------------------------------
1             1                 1 
2             1                 2
3             1                 3 
4             2                 1
5             2                 3
I would like to run a stored procedure with an input of a user_id which will bring back all leagues entered by the user, the host of each of those leagues, how many total players have entered in each league and what position the user is in for each of those leagues(sorted by total score).
At the moment I have:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_private_leagues`(IN v_user_id INT)
BEGIN

    DECLARE userteamid INT;

    # Retrieve user team from a user_id
    SELECT user_team_id INTO userteamid
    FROM user_team 
    WHERE user_id = v_user_id;

    # Retrieve private league name and host user (for a userteam)
    SELECT private_league.league_name, private_league.host_user
    FROM   user_team_private_league_M2M
    INNER JOIN privateleague
    ON user_team_private_league_M2M.priv_league_id=private_league.priv_league_id
    WHERE  user_team_id = userteamid;

END

This query does not include the total number of players for each league and the current position of the user
I have created a query to bring back the total users for each private league, with no user filter like so:
SELECT private_league_id, COUNT(*) AS total_users
FROM   classicseasonmodel_classicseasonuserteamprivateleague
GROUP BY private_league_id;

A query for the user's current position can be worked out by using the answer to this question and using total_score.
I am extremely stuck with this at the moment - the perfect result from the SP will be as follows:
CALL user_private_leagues(3); (user id of BarneyB)
priv_league_name     current_position     total_users     host_user
-----------------------------------------------------------------------
Lisa's League               3                 3           LisaGreen17 
Barney's Bonanza            2                 2             BarneyB
Thanks!

A: Let take it step by step....
First ID, Count for pleague 
SELECT private_league_id, COUNT(*) AS total_users
FROM   classicseasonmodel_classicseasonuserteamprivateleague
GROUP BY private_league_id;

Now add in Name and host user
SELECT PL.league_name, LC.uCNT, PL.host_user  
FROM (SELECT private_league_id AS pID, COUNT(*) AS uCNT
      FROM   classicseasonmodel_classicseasonuserteamprivateleague
      GROUP BY private_league_id ) AS LC
LEFT JOIN private_league PL ON PL.priv_league_id = LC.pID

Now add in host user name
SELECT PL.league_name, LC.uCNT as total_users, hu.name as host_user
FROM (SELECT private_league_id AS pID, COUNT(*) AS uCNT
      FROM   classicseasonmodel_classicseasonuserteamprivateleague
      GROUP BY private_league_id ) AS LC
LEFT JOIN private_league PL ON PL.priv_league_id = LC.pID
LEFT JOIN user hu ON PL.host_user = hu.user_id

Don't know where current position is.

This query will give users and position for each team, join to this and limit by user id to get one users position for each team:
select UT.user_id, 
       UT.team_id, 
       ROW_NUMBER() OVER (PARTITION BY team_id ORDER BY total_score DESC) AS team_position
from private_league L
join user_team_private_league_M2M LJ ON L.priv_league_id = LJ.priv_league_id 
join user_team UT ON LJ.user_team_id = UT.user_team_id


A: Sorry but I didn't create the DB to test the SQL below. But you can start from there. No need for UNION. I didn't understand the business rule to compute the user's position in the league, since it may come from the team or the user. 
select priv_league_id, league_name, host_user_name, count(*) as total_users
from (
      select A.priv_league_id, A.league_name, D.name as host_user_name, B.user_team_id, C.user_id, D.
      from private_league A
      join user_team_private_league_M2M B
      on A.priv_league_id = B. priv_league_id
      join user_team C
      on B.user_team_id = C. user_team_id
      join user D
      on A.host_user = D.user_id
) D
group by priv_league_id

