write efficient mysql statement how represent in a different table form

(sorry guys, i think i never specify my requirements properly)

|origin   |destination |flight_type |price|
|melbourne|sydney      |one way     |100  |
|melbourne|sydney      |one way     |120  |
|melbourne|sydney      |one way     |150  |
|melbourne|sydney      |return trip |250  |
|melbourne|sydney      |return trip |300  |
|melbourne|sydney      |return trip |350  |

how do i write a most efficient single mysql query to get the lowest price for each way, a table result like below:

|origin    |destination  |oneway_price(lowest) |ret开发者_如何学Pythonurn_trip_price(lowest)|
|melbourne |sydney       |100                  |250                      |
|

I am thinking of finding the lowest fare for one way(group by origin,dest,flight type & flight_type = one way), and left join back to itself with lowest fare for return trip(group by origin,dest,flight type & flight_type = return) and where has the same origins destinations

---

Try this out. I think that this should probably work for your requirements

EDIT: Updated based on the updated question

SELECT 
    origin, 
    destination, 
    MIN(CASE flight_type WHEN 'one way' THEN price END) as 'oneway_price',
    MIN(CASE flight_type WHEN 'return trip' THEN price END) as 'return_price'
FROM table
WHERE origin = 'melbourne' 
AND  destination  = 'sydney'
GROUP BY origin, destination 

Note: The WHERE clause can be omitted if you want to do it for all combinations of origin + destination in your table

---

You can also do it with a JOIN:

select 
  f1.origin, f1.destination, f1.price as price_oneway,f2.price as price_return
from flights f1
left join flights f2 
  on f1.origin=f2.origin and f1.destination=f2.destination and f2.flight_type='return trip'
where f1.flight_type='one way'

Note that this will only produce results for combinations of (origin,destination) which have a one way price; it will give a NULL for price_return, if there is no corresponding return price.

It also distinguishes flights with destination and origin reversed; this may or may not be what you want.

Also, this will produce results for all flights. You can further restrict in WHERE if you need to.

---

I'd probably go with a SUBQUERY like this.

SELECT
    origin,
    destination,
    price as oneway_price,
    (SELECT price FROM my_table WHERE origin = 'sydney' AND destination = 'melbourne') as return_price
FROM
    my_table
WHERE
    origin = 'melbourne' AND
    destination = 'sydney'

It should be fairly simple if you pass city names as variables.

This might work too, should check first though:

SELECT
    t.origin,
    t.destination,
    t.price as oneway_price,
    (SELECT price FROM my_table WHERE origin = t.destination AND destination = t.origin) as return_price
FROM
    my_table t
WHERE
    origin = 'melbourne' AND
    destination = 'sydney'