mysql filter by result of sub query

Question

i have this Query :

SELECT a.*, a.id AS id_player, (SELECT COUNT(id) FROM `vd7qw_footsal_goals` WHERE a.id = id_player AND g.id = id_group) AS goals, 
team.* FROM `vd7qw_footsal_players` AS a 

LEFT JOIN vd7qw_footsal_teams AS team ON team.id= a.id_team

LEFT JOIN vd7qw_footsal_teamofgroup AS tog ON tog.id_team = team.id 4

LEFT JOIN vd7qw_footsal_groups AS g ON g.id = tog.id_group
WHERE g.id IN (SELECT id_group from `vd7qw_footsal_groupofleague` WHERE id_league = 2) 
AND (a.state IN (1)) AND goals > 0 ORDER BY goals DESC

and i want to filter its resaults by players that have goals

the above query have error in this part goals > 0 i don't know how to do that can any1 help me ?

Answer 1

Try this:

SELECT * FROM 
(SELECT a.*, a.id AS id_player, (SELECT COUNT(id) FROM `vd7qw_footsal_goals` WHERE a.id = id_player AND g.id = id_group) AS goals, 
team.* FROM `vd7qw_footsal_players` AS a 

LEFT JOIN vd7qw_footsal_teams AS team ON team.id= a.id_team

LEFT JOIN vd7qw_footsal_teamofgroup AS tog ON tog.id_team = team.id 4

LEFT JOIN vd7qw_footsal_groups AS g ON g.id = tog.id_group
WHERE g.id IN (SELECT id_group FROM `vd7qw_footsal_groupofleague` WHERE id_league = 2) 
AND (a.state IN (1))) AS A WHERE goals > 0 ORDER BY goals DESC