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

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
Copy code

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.

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.