I am working with PostgreSQL.

My idea is to group players who appear more than 3 times in table-a.

table-a = <player_id, year_world, c, d, e>

With the first subquery I get player_id and the number of times that player played a world cup. And with the NATURAL JOIN I recover the year or other attributes in the tuples that player appears.

The following query works but I want to optimize it:

SELECT player_id , year_world
FROM    (SELECT player_id , count(player_id ) player_id_count
        FROM <table-a> 
        GROUP BY player_id 
        HAVING count(player_id ) > 3) playersMoreThree
NATURAL JOIN <table-a>

I would like to know if it is possible not to use the NATURAL JOIN to obtain the other attributes of the table. Because when I put the rest of the attributes I want to project into SELECT and in the GROUP BY I get an empty table.

Keep in mind that the attribute that is added to the SELECT of the subquery of the GROUP BY has to be determined by the attribute that we are grouping. That is, for each player_id value, I can always have the same value of the new attribute, if for a same value of player_id I have different values of the new attribute, this is not determined. For add an attribute that is not correlated by what group I use NATURAL JOIN or another solution.

The following query without NATURAL JOIN does not work for me:

SELECT player_id, count(player_id ) player_id_count, year_world, c, d, e
FROM <table-a> 
GROUP BY player_id, year_world, c, d, e
HAVING count(player_id ) > 3

What would be the most efficient way in DML?