SELECT JOIN same table prevent duplicates MySQL 8.x

Question

I have a table called 'price' with the following rows

idRequest | idPair | idExchangePlatform | lastPrice
    1         1               1               95
    1         1               2               100
    1         1               3               40
    2         1               1               80
    2         1               2               250
    2         1               3               10
    3         1               1               110
    3         1               2               300
    3         1               3               60
    1         2               2               800
    1         2               3               1300
    2         2               2               1110
    2         2               3               1950

i need to make a SELECT QUERY WHERE idRequest and exchangePair match with the same table values but different idExchangePlatform

For example for this set of values:

idRequest | idPair | idExchangePlatform | lastPrice
    1         1               1               95
    1         1               2               100
    1         1               3               40

i need to get the following output:

idRequest | idPair | idEP_a | idEP_b | lastPrice_sum
    1         1         1       2           195
    1         1         1       3           135
    1         1         2       3           140

i'm using this query but it duplicates the result because is matching the idExchangePlatform in twisted order.

select p1.idRequest, p1.idPair, p1.idExchangePlatform AS idEP_a, p2.idExchangePlatform AS idEP_b, (p1.lastPrice + p2.lastPrice) AS lastPrice_sum
from price as p1
JOIN price AS p2
ON p1.idRequest = p2.idRequest AND p1.idPair = p2.idPair AND p1.idExchangePlatform <> p2.idExchangePatform;

This is the output i get from this query:

idRequest | idPair | idEP_a | idEP_b | lastPrice_sum
    1         1         1       2           195
    1         1         1       3           135
    1         1         2       3           140
    1         1         2       1           195
    1         1         3       1           135
    1         1         3       2           140

Is there a way to prevent this with some CLAUSE?

Answer 1

You can use GROUP BY

select p1.idRequest, p1.idPair, p1.idExchangePlatform AS idEP_a, p2.idExchangePlatform AS idEP_b, (p1.lastPrice + p2.lastPrice) AS lastPrice_sum
from price as p1
JOIN price AS p2 ON (p1.idRequest = p2.idRequest AND p1.idPair = p2.idPair AND p1.idExchangePlatform <> p2.idExchangePlatform)
GROUP BY p1.idRequest,p1.idPair, (p1.idExchangePlatform + p2.idExchangePlatform )

The Result: