Mido Mido - 1 year ago 68
MySQL Question

Calling the minimum value using join function mysql php

I have two tables:

id, hotel

id , hotel, type, stay, price

At the second table there are more than one row with the same room type at different prices depending on the room meal (stay).

How can I call just for one row from the second table which has the minimum price for this room type?

My code as following

select h.hotel
, r.hotel
, r.stay
, r.type
, r.price
from hotels h
JOIN rooms r
ON r.hotel = h.hotel
where r.type = '$rtype1
BY r.hotel
BY r.price asc

$rtype is the value sent by the visitor through the input field

Answer Source

Basic way to do it would be to use a sub query to get the first item for each hotel, and then join that to get the other details. However in this case this isn' such a good idea as several rooms can probably have the same low price. But something like this:-

SELECT h.hotel,
FROM hotels h 
    SELECT hotel, 
            MIN(price) AS min_price
    FROM rooms
    WHERE r.type = '$rtype1'
    GROUP BY hotel
) sub0
ON sub0.hotel = h.hotel
LEFT OUTER JOIN rooms r ON r.hotel = sub0.hotel AND AND r.price = sub0.min_price AND r.type = '$rtype1'

Another was is to (ab)use the GROUP_CONCAT and SUBSTRING_INDEX functions. This gets all the details for each room for each hoten in price order, separated by a comma (default from GROUP_CONCAT) and then uses SUBSTRING_INDEX to get the first details. This struggles when the items may naturally contain the delimiter.

SELECT h.hotel,
        SUBSTRING_INDEX(GROUP_CONCAT(r.stay ORDER BY r.price), ',', 1),
        SUBSTRING_INDEX(GROUP_CONCAT(r.type ORDER BY r.price), ',', 1),
        SUBSTRING_INDEX(GROUP_CONCAT(r.price ORDER BY r.price), ',', 1)
FROM hotels h 
LEFT OUTER JOIN rooms r ON r.hotel = h.hotel AND r.type = '$rtype1' 
GROUP BY h.hotel 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download