MySQL Forums
Forum List  »  Newbie

Problem with my query MAX function
Posted by: Stéphan Dorion
Date: June 01, 2022 09:25PM

Hi,
I am learning mySQL and a newbie. I am trying to solve a problem and I am stock very bad and would need help please…

I have 3 tables;

Store
idStore idProd
1 --- 234
2 --- 532
3 --- 143

Yield
idYield Descrip
1 --- Bad
2 ---Average
3 --- Good

Transactions
Result idStore
1 --- 1
2 --- 1
3 --- 1
1 --- 2
2 --- 2
1 --- 3

I have to do a query that will return only the best result (transaction) for each store giving the idStore, Result, Descrip for each store without using the GROUP BY function
I cannot find a way to include all 3 stores. My result return only one store, the one with the best result but I need the best result for each 3 stores

Here my code:
mysql> SELECT S.idStore, S.idStore, S.Descrip
-> FROM Store S, Transaction T, Yield Y
-> WHERE (S.idStore = T.idStore) AND
-> ((SELECT MAX(T.Result) FROM Transaction) = Y.Result) AND
-> (T.Result = Y.idYield) ;

Here my result:

Store
idStore Result Descrip
1 ---3 ---Good

But this is the result I need.

idStore Result Descrip
1 --- 3 --- Good
2 ---2 --- Average
3 ---1 --- Bad

Please help me.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.