Mysql – Display the the result of a substraction in a MySQL query (1 table)

MySQL

I've made a table that looks like this:

stop (table name)
-cityName {PK}
-schedule {PK}
-price
-routeNo

Basicly the customer wants to know what's the cost difference between 2 different stops by giving the city name of both A and B and the routeNo (routeNo is the same for both stops)

Now I have to make a MySQL query in order to display that difference:

Say City A cost 10$ and City B cost 15$ The returned value should be 5$ (price of B – price of A)

Sorry about the rookie question but I'm lost I've tried:

SELECT (b.price - a.price) AS Returned
FROM stop
WHERE a.cityName = 'A' AND b.cityName = 'B' AND routeNo = 'x';

Best Answer

No row will satisfy cityname = 'A' and cityname = 'B'. You must compare different rows. Modifying your query to:

SELECT ABS(s1.price - s2.price) AS Returned
FROM stop AS s1
JOIN stop AS s2
    ON s1.cityname = 'A'
    AND s2.cityname = 'B'
    AND s1.routeNo = s2.routeNo
WHERE a.routeNo = 'x'

will determine the difference in price between 'A' and 'B'. I added ABS so that it does not matter if 'A' or 'B' is cheaper.

Related Question