MySQL JOIN table problem and aggregation

When you use GROUP BY, the columns in the select list must satisfy one of the following:

• Column named in GROUP BY (like btv.id_user
  
  in your example)
• The column is inside an aggregate function (for example MIN( btv.cas )
  
  )
• Column is the functional dependency of the column (s) that you named in GROUP BY.

This is a mistake in your request: btv.id_zavod

has many meanings for each value btv.id_user

. This does not satisfy functional dependency. For each value, there id_user

must be only one value in id_zavod

for it to be a functional dependency.

On some brands of databases, this query will actually give you an error. MySQL is more flexible by trusting you to only specify columns in the select list, which are functional dependencies of the columns (s) that you named in the GROUP BY.

Here's a query that returns what you want, the MIN value btv.cas

for id_user

, with the corresponding value btv.id_zavod

:

SELECT b1.id_user, b1.id_zavod, b1.cas
FROM btv AS b1
 JOIN btu ON (b1.id_user = btu.id_user)
 JOIN race ON (b1.id_zavod = race.id_zavod)
 LEFT OUTER JOIN btv AS b2 ON (b1.id_user = bt2.id_user AND 
   (b1.cas > b2.cas OR (b1.cas = b2.cas AND b1.primarykey > b2.primarykey))
WHERE race.type = '8' AND b1.id_user = '607'
 AND b2.id_user IS NULL;

      

        
        
        
      

    

In other words, you need to make your join as before, but then re-join it to btv

see if there is another row with the same value id_user

and less value in cas

. Use the BUILT-IN CONNECTION because you are looking for a case where there is no match. You can check this with b2.id_user IS NULL

, because OUTER JOIN makes all columns NULL when there is no match.

Note that there may be relationships, so we add an additional term using the primary key as a tiebreaker.

You don't need to use GROUP BY in this query. This is implicitly taken care of, because there will only be one row that satisfies the OUTER JOIN condition.