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'