The Join is incompletely specified. It brings in all x and y.
Don't use comma join syntax (...From x,y Where...); it's ambiguous and not compatible with some recent SQL enhancements.
Use explicit join syntax ...
Select X.idStore, X.idResult, Y.Descrip
FROM Transactions X
JOIN Yield Y USING(idStore)
WHERE X.Store = (
SELECT Max(P2.Store)
FROM Transactions AS P2
WHERE X.idResult = P2.idResult
);
It's not clear to me, though, that max(store) is right; aren't you looking for max(result) or max(yield)
If so, post show create table results for the three tables and enough Inserts to replicate the problem.
And, I notice you copy & paste from the mysql client program. It's a good way to run queries, but a terrible development environment. Get used to composing your queries in a good text editor.
Edited 1 time(s). Last edit at 06/07/2022 11:35AM by Peter Brawley.