Find minimum value AND include correct column-values

Question

I am trying to get the minimum price per travel and know which travel-details correspond to that minimum price per travel.

For this I have tried all kind of variations with subqueries, joins etc. but since there is not 1 primary key I cannot figure it out.

What I'm trying to achieve is get the travel with the lowest price, and then included in the record the details of the travel with that lowest price.

  SELECT travel_id, persons, days, MIN(`price`) AS `price` 
  FROM travel_details
  WHERE 1 
  GROUP BY `travel_id`
  HAVING MIN(price);

Simple version of my table-columns, columns are: travel_id, persons, days, price

Those columns together form the primary key.

A travel can be booked for various persons, days and prices. It can also occur that there are multiple price-options for the same combination of travel_id, persons, and days.

E.g.,

100, 2,   4, **250**

100, 2,   4, **450**

100, 2, **5**, 450

101, 2,   4,   190

101, 2,   5,   185

Being travel_id 100 for 2 persons for 4 persons.

What I would like to achieve is return: 100, 250, and then with correct corresponding values:

100, 2, 4, 250

101, 2, 5, 185

Now my result just mixes all the other data. When I include those columns in the group by, it will not only group on travel_id anymore, but also e.g., on persons. Then it will return all combinations for a travel_id and persons.

Any idea how to approach this?

Answer 1

    Select a.travel_id, a.persons, a.days, a.price from  travel_details a 
JOIN (Select travel_id,MIN(Price) as p from travel_details group by travel_id) b 
on b.travel_id=a.travel_id and b.p=a.price

The above query uses self join. Derived table b will contain travel_id along with min price.