Jump to content
Sign in to follow this  

leaving out null rows

Recommended Posts

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)

Share this post

Link to post
Share on other sites

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 ...;

Share this post

Link to post
Share on other sites

A human-readable version of your query:

    player_details.player_id AS P_id,
    CONCAT(player_details.Fname, ' ', player_details.Lname) AS pname,
    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
    AND team_players.Team_catId = '1'
    team_players.position_id ASC



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.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.