Question 41660 in PHP Question posted in
Official manual can be found here.

Select row if at least one possible SUM of its children equal a given number? – PHP

I am trying to filter a set of appartments by computing every possible surface the building has got, and then checking if it matches my criterias or not.

Example : I have one building A that is composed of 3 appartments of 200m² each, let’s name them 1, 2, and 3.

In my search, I should be able to retrieve that building if my criterias meet these given cases :

  • I’m looking for 200m² (We have three appartments that match this criteria)
  • I’m looking for 400m² (We have a few possible SUMS of surface in the building that would match, whether it’s 1+2, 1+3, 2+3 doesn’t matter)
  • I’m looking for 600m² (We have the SUM of all surfaces of the building, 1+2+3)

I am able to answer to the first case with a MIN(), so I get the smallest surface available. I am also able to answer to the last case because I get the max available surface possible with a SUM() of all appartments.

But the second case is troubling me, I don’t know if i can compute these "Possible SUMS", as I’d call them, inside a query.

Here’s the SQL I’ve got so far, knowing well that it doesn’t answer the second case :

SELECT DISTINCT building.* 
FROM building AS building   
    LEFT JOIN appartment a ON a.id_building = building.id 
WHERE (
        SELECT MIN(a2.surface) 
        FROM appartment a2 
            INNER JOIN building ON a2.id_building= building.id 
      ) >= 399
AND (
        SELECT SUM(a2.surface) 
        FROM appartment a2 
            INNER JOIN building ON lot.id_building= building.id 
    ) <= 401

I tried to work with the whole set of results with PHP rather than in SQL but it’s not my prefered option because it would mean the redoing of a lot of work that hasn’t been done by me, so it quickly got harder. I also read about HAVING statements but I don’t understand where I should put them and what the condition inside should be.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I finally managed to find something using a stored function. I use this cursor with the building id as parameter to loop through every appartement of said building, sorted by surface. Then, for each loop, i add the surface to my total, and check if I am or not in my interval of given criterias. If yes, set return value to true, if not, return value stays false.

    Here it goes, I hope it helps if someone is stuck as I was :

    CREATE DEFINER=`homestead`@`%` FUNCTION `buildingHasSurface`(`surface_min` INT, `surface_max` INT, `building_id` INT) RETURNS TINYINT(1)
BEGIN
  DECLARE curseurFini INT;
  DECLARE valide INT;

  DECLARE surface_totale INT;
  DECLARE surface_row INT;

  DECLARE curs CURSOR FOR SELECT surface_dispo FROM appartement WHERE id_building = building_id ORDER BY surface_dispo ASC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET curseurFini = 1;

  OPEN curs;

  SET curseurFini = 0;
  SET surface_totale = 0;
  SET valide = 0;
  
  REPEAT
    FETCH curs INTO surface_row;
      IF curseurFini = 0 THEN
        SET surface_totale = surface_totale + surface_row;
        IF surface_totale >= surface_min 
        AND surface_totale <= surface_max
        THEN 
        SET valide = 1;
      END IF;
    END IF;
  UNTIL curseurFini END REPEAT;

  CLOSE curs;





  RETURN valide;
END$$

  2. 0

    maybe something like this, but i’m not very sure about your table structure

        SET @totalSurface= 0;
    SELECT @totalSurface:=@totalSurface+`surface`, `id`, `building` FROM `apartment`
    WHERE @totalSurface=400
    GROUP BY `building` ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search