CODEWITHSUNDEEP
sqlmysqljoin
beny
beny

Reputation: 456

Problem with table JOIN in MySQL and aggregation

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 |

Upvotes: 1

Views: 666

Answers (5)

Pianosaurus
Pianosaurus

Reputation: 5758

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

Upvotes: 0

Shinhan
Shinhan

Reputation: 2830

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

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562348

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.

Upvotes: 0

Jason Michael
Jason Michael

Reputation: 344

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'

Upvotes: 0

user12861
user12861

Reputation: 2426

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?

Upvotes: 1

Related Questions