SQL list all apartments with actual owner

Question

I'm trying to use a query to list all apartments with actual owner.

My apartment table look like this:

building | apartmentNbr | owner |      start |        end
      b1 |           a1 |    o1 | 2009-08-13 | 2010-08-13
      b1 |           a1 |    o2 | 2010-08-14 | 2019-01-01
      b1 |           a2 |    o3 | 2009-01-03 | 2010-01-03
      b1 |           a2 |    o4 | 2010-01-04 | 2010-08-13
      b1 |           a2 |    o5 | 2010-08-14 | 9999-12-31
      b1 |           a3 |    o6 | 2010-08-14 | 2019-12-31
      b1 |           a3 |    o7 | 2020-02-14 | 9999-12-31
      b2 |           b1 |    o8 | 2012-08-14 | 9999-12-31
...

With my query

SELECT *
FROM Appartments
WHERE CURDATE() BETWEEN startDate AND endDate

I only have the occupied apartments but I would like to have also free like this:

building | apartmentNbr | owner
      b1 |           a1 |  null
      b1 |           a2 |    o5
      b1 |           a3 |  null
      b2 |           b1 |    o8

How can I fix this?

Answer 1

Do a GROUP BY. Use a case expression to get the current owner (or null).

SELECT building, apartmentNbr,
       max(case when CURDATE() BETWEEN startDate AND endDate then owner end)
FROM Appartments
GROUP BY building, apartmentNbr