leaving out null rows

[shaddf]

how can i leave out all the null rows  from this result of query below;iam trying to get the list of player names and their playing position

mysql> select f0.player_id as P_id,concat (f0.Fname,' ',f0.Lname)as pname,f1.position_id,f2.position from( select *  from Soka_player_details_tbl) as f0 left join (select * from Soka_players_team_tbl where Soka_players_team_tbl.SeasonID=(select SeasonID from tbl_season where Publish='1')and Team_catId= '1'  )as f1 on f1.player_id=f0.player_id left join (select * from Soka_position_tbl )as f2 on f1.position_id=f2.position_id order by position_id asc;
+------+----------------------------------------------------+-------------+--------------+
| P_id | pname                                              | position_id | position     |
+------+----------------------------------------------------+-------------+--------------+
|    5 |  Performance nutritionist  Assistant manager       |        NULL | NULL         |
|    8 |  Kit manager  Equipment manager                    |        NULL | NULL         |
|    6 |  Head of performance  Head of medical services     |        NULL | NULL         |
|    4 |  Masseur  Assistant kit manager                    |        NULL | NULL         |
|    7 |  Assistant fitness coach Under-21s assistant coach |           1 | Goal keepers |
|    1 |  Manager  First-team coach                         |           1 | Goal keepers |
|    2 |  Goalkeeping coach  Fitness coach                  |           2 | Defenders    |
|    3 |  Club doctor  First-team physiotherapist           |           3 | Midfielders  |
|    9 |  Football analyst  Academy manager                 |           4 | Forwards     |
+------+----------------------------------------------------+-------------+--------------+
9 rows in set (0.00 sec)

[Jacques1]

Filling up missing rows with NULLs is the whole point of a left join. If you don't want that, don't use it.

 

Your excessive use of subqueries is also a bit strange. You know that you can operate on tables directly, right?

SELECT col1, col2, ... FROM table1 JOIN table2 ON ... WHERE ...;

[Jacques1]

A human-readable version of your query:

SELECT
    player_details.player_id AS P_id,
    CONCAT(player_details.Fname, ' ', player_details.Lname) AS pname,
    positions.position_id,
    positions.position
FROM
    Soka_player_details_tbl AS player_details
    JOIN Soka_players_team_tbl AS team_players ON team_players.player_id = player_details.player_id
    JOIN tbl_season AS seasons ON seasons.SeasonID = team_players.SeasonID
    JOIN Soka_position_tbl AS positions ON positions.position_id = team_players.position_id
WHERE
    seasons.Publish
    AND team_players.Team_catId = '1'
ORDER BY
    team_players.position_id ASC
;

(untested)

 

Your table and column names definitely need to be improved (rAnDOM ChAnGES oF CHarActER caSE and random tbl_ prefixes and suffixes aren't very helpful). I've used aliases for now.