But this returns all rows, not only the ones with minimum area value, if I add "GROUP BY pid" to the main query, only rows with bigger area values are returned (I currently have 2 different values for area). How could it be fixed?
i.id AS pid
f.pid = i.id
f.area = (SELECT MIN(area) FROM files WHERE pid = i.id)
your query is fine, and requires no GROUP BY
are you sure it isn't a data problem?
do you have a test script of sample rows that illustrate the problem you're seeing?
Thank's r937, it was a data problem after all. I was also using varchar to store area values, because of that (I guess), values were compared as strings.