Problem with table JOIN in MySQL and aggregation

Question

I have three tables. This query will write down the right answer (x-lines for btv.id_user with appropriate btv.cas and race.id_zavod

SELECT `btv.id_user`, `btv.id_zavod`,`btv.cas`
FROM `btv`
JOIN `btu` ON `btv.id_user` = `btu.id_user`
JOIN `race` ON 'btv.id_zavod' = `race.id_zavod`
WHERE `race.type` = '8' AND `btv.id_user` = '607'

Result:

| 607 |  512 | 03:15:58 |  
| 607 |  730 | 03:01:18 |  
| 607 |  164 | 03:07:26 |  
| 607 |  767 | 02:58:31 |  
| 607 | 1147 | 03:06:47 |  
| 607 | 1149 | 03:09:41 |  
| 607 | 1178 | 03:24:20 |

But when I try to aggregate it to one row by the id_user it return correct min btv.cas but wrong join wrong race.id_zavod

SELECT `btv.id_user`, `btv.id_zavod`, MIN( `btv.cas` )
FROM `btv`
JOIN `btu` ON `btv.id_user` = `btu.id_user`
JOIN `race` ON 'btv.id_zavod' = `race.id_zavod`
WHERE `race.type` = '8' AND `btv.id_user` = '607'
GROUP BY `btv.id_user`

Result:

| 607 | 512 | 02:58:31 |

Answer 1

did not help, it return the same wrong result

Answer 2

The query you have written:

SELECT `btv.id_user`, `btv.id_zavod`, MIN( `btv.cas` )
FROM `btv`
JOIN `btu` ON `btv.id_user` = `btu.id_user`
JOIN `race` ON 'btv.id_zavod' = `race.id_zavod`
WHERE `race.type` = '8' AND `btv.id_user` = '607'
GROUP BY `btv.id_user`

won't run. You need a group by id_zavod or something. Can you tell us what query you are really running? And what result set you expect?

Answer 3

Try:

SELECT `btv.id_user`, `btv.id_zavod`, MIN( `btv.cas` )
FROM `btv`
Inner JOIN `btu` ON `btv.id_user` = `btu.id_user`
Inner JOIN `race` ON 'btv.id_zavod' = `race.id_zavod`
WHERE `btv.id_user` = '607'
GROUP BY `btv.id_user` having `race.type` = '8'

Answer 4

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

• Column is named in the GROUP BY (e.g. btv.id_user in your example)
• Column is inside an aggregate function (e.g. MIN( btv.cas ) )
• Column is a functional dependency of the column(s) you named in the GROUP BY.

This is the error in your query: btv.id_zavod has many values for each value of btv.id_user. This does not satisfy functional dependency. There must be only one value in id_zavod for each value of id_user for it to be a functional dependency.

In some database brands, this query would actually give you an error. MySQL is more flexible, trusting you to name only columns in the select-list that are functional dependencies of the column(s) you named in the GROUP BY.

Here's a query that returns what you want, the MIN value of btv.cas per id_user, with the corresponding value of 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 do your join as before, but then join that to btv again, to see if there's another row with the same id_user value and a smaller value in cas. Use an OUTER JOIN, because you're looking for the case where there is no match. You can test for that with b2.id_user IS NULL, because OUTER JOIN makes all columns NULL when there is no match.

Note that there could be ties, so we add the extra term using the primary key as the tiebreaker.

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

Answer 5

I'm not sure what you are trying to accomplish, but are you maybe looking for the GROUP_CONCAT function??

Answer 6

Thanx for stunning answer, I'will remember these informations. But the problem stills :P

Tried the query with the second join of the same table, but none of the column was NULL :P and it wrote me all records (even mupltipletimes so I had to use DISTINCT)

Here is the query

SELECT DISTINCT b1.id_user bt1, b2.id_user bt2, 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.idp)
 LEFT OUTER JOIN btv AS b2 ON (b1.id_user = b2.id_user AND 
   ( b1.cas > b2.cas OR (b1.cas = b2.cas AND b1.id > b2.id)))
WHERE race.type = '8' AND b1.id_user = '607'

The result:

bt1     bt2  id_zavod  cas
607     607  512  03:15:58
607     607  730  03:01:18
607     607  164  03:07:26
607     607  767  02:58:31
607     607  1147  03:06:47
607     607  1149  03:09:41
607     607  1178  03:24:20

Answer 7

Are you simply trying to fetch the lowest btv.cas value and its other corresponding columns? If so, you could just order by btv.cas and limit to 1 hit:

SELECT `btv.id_user`, `btv.id_zavod`,`btv.cas`
FROM `btv`
JOIN `btu` ON `btv.id_user` = `btu.id_user`
JOIN `race` ON 'btv.id_zavod' = `race.id_zavod`
WHERE `race.type` = '8' AND `btv.id_user` = '607'
ORDER BY `btv.cas` LIMIT 1