Jump to content

leaving out null rows


shaddf
Go to solution Solved by Jacques1,

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)

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

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

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

×
×
  • 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.