tsql - How to select all rows from one table and any matches from another table if they exist? -

i have database 2 tables, teams , players:

teams:               teamid  | teamname  ------------------  |1      | team | |2      | team b | |3      | team c | ------------------  players:  playerid  | name      | teamid | position ------------------------------------------  |1        | anthony j | 1      | guard   | |2        | sam k     | 2      | guard   | |3        | jack p    | 2      | forward | |4        | carlos b  | 2      | forward | ------------------------------------------ 

i looking select column names of teams in database, regardless of whether have players or not. next column should display name if position 'forward', or null if not. there should row each 'forward' player on team.

the query using data is:

select t.teamname,  case when p.position = 'forward'     p.name end forwardplayer  teams t left join players p on t.teamid = p.teamid 

all of data require resides in data set, except there row null team have forward, i.e:

teamname  | forwardplayer ------------------------- |team   | null        | |team b   | jack b      | |team b   | carlos b    | |team b   | null        | |team c   | null        | 

i realise 4th row exists because there player team doesn't meet case condition, result null.

putting clause on outer query restrict data set 'forward' players omit teams without forward players. need of teams names present in data set.

using nested select obtain work if there 1 foward each team.

is there way omit these null rows teams have player meets condition?

don't case. straight want restrict players forward ones, join:

select t.teamname, p.name teams t left join players p on t.teamid = p.teamid   , p.position = 'forward'