MySQL - How do I make the HAVING query pick the lowest value listing AND also compare something else-CodePudding

My function wants to check if the number of bedrooms match what the user inputs in to the function AND also check to make sure it is the listing with the lowest price. So for instance "cheapest_property_with_n_bathrooms (2)" would check all the listings where the property has 2 bedrooms, and then return the cheapest one. My code works, but doesnt return anything because HAVING needs to compare two values, and the part after the AND isn't comparing anything.

DELIMITER $$
DROP FUNCTION IF EXISTS cheapest_property_with_n_bedrooms $$
CREATE FUNCTION cheapest_property_with_n_bedrooms (numBedrooms INT)
RETURNS INT DETERMINISTIC
    BEGIN
        DECLARE PropertyID INT;
        DECLARE bedrooms INT;
        SET PropertyID = (SELECT Property.property_id
                            FROM Property
                            JOIN Listing ON Listing.property_FK = Property.property_id
                            JOIN Amenities ON Amenities.property_FK = Property.property_id
                            GROUP BY Amenities.num_bedrooms
                            HAVING Amenities.num_bedrooms = numBedrooms AND MIN(Listing.price) 
                            ORDER BY Property.property_id DESC
                            LIMIT 1
                            );
                            
        RETURN PropertyID;
    END$$
DELIMITER ;

CodePudding user response：

When I am trying with your SQL query it gives me an syntax error.

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_160534543.Property.property_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I have resolved by that adding the Amenities.property_FK and Listing.price to the GROUP BY.

Instead of getting the least price using MIN function you can ORDER BY the price.

And When group by these columns, We need to change the ORDER BY to ASC to get the least price one.

Can you try with this SQL query

SELECT Property.property_id
    FROM Property
    JOIN Listing ON Listing.property_fK = Property.property_id
    JOIN Amenities ON Amenities.property_FK =Property.property_id
    GROUP BY 
    Amenities.property_FK,Amenities.num_bedrooms,Listing.price
    HAVING Amenities.num_bedrooms = numBedrooms
    ORDER BY Listing.price ASC LIMIT 1;

try with dbfiddle

You can refer this to get an example about GROUP BY using with JOIN tables.