Jump to content
Sign in to follow this  
shaddf

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:

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

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.