Problem with my query MAX function
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.
Subject
Written By
Posted
Problem with my query MAX function
June 01, 2022 09:25PM
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.