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`
GROUP BY `travel_id`
100, 2, 4, **250**
100, 2, 4, **450**
100, 2, **5**, 450
101, 2, 4, 190
101, 2, 5, 185
You can use
IN() to do this:
SELECT * FROM travel_details t WHERE (t.travel_id,t.price) IN(SELECT s.travel_id,min(s.price) FROM travel_details s GROUP BY s.travel_id) GROUP BY t.travel_id; // this last group-by is to filter doubles when there are multiple records per travel with the lowest price.