0

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?

2
  • 1
    Change the condition p1.idExchangePlatform <> p2.idExchangePatform to p1.idExchangePlatform < p2.idExchangePatform Apr 1 at 9:50
  • Sounds like you want a pivot. See the tag [pivot-table] in stackoverflow.com
    – Rick James
    Apr 1 at 18:38
0

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.