Crafting a query for distinct data from two fields where one is often NULL

Question

hi!

I have a table for players, a table for teams, and a table for team_players (using SQLServer 2005). Not all players are on a team. I would like to craft a query that will return one row for each non-team player, and one row for each team (i.e. there is one result row representing all 20 players on the Knicks, and that result row contains just the Knicks' team_id, but all non-team players get their own rows with unique player_id in the results set).

I am currently trying to have my result set consist of just one column, and am doing it like so:

SELECT DISTINCT ISNULL(tp.team_id, p.player_id) FROM players p 
LEFT JOIN team_players tp ON tp.player_id = p.id

My question is: how can I allow this query to be ordered by teams with the most players DESC, and then by player name alphabetical for the non-team players? Is this possible with my current query base? Should I use a different method, like perhaps a UNION, to make this possible?

Answer 1

As in Martin's case, this is untested:

;with cteCombineData as (
    select t.id, null as player_name, count(*) as player_count
        from team t
            inner join team_players tp
                on t.id = tp.team_id
        group by t.id
    union all           
    select p.id, p.player_name, 0 as player_count 
        from players p
            left join team_players tp
                on p.id = tp.player_id
        where tp.player_id is null
)
select id
    from cteCombineData
    order by player_count desc, player_name