0

Assume I have two tables, one room and rented. How can I get mysql join result like the table below.

enter image description here

display the availability for each room_id given quantity from the first column and booked status on second table for each date given date range.

user3392555
  • 43
  • 2
  • 7

1 Answers1

0

Firstly, make sure from and until are MySQL DATE types, and not stored as strings.

If those columns are MySQL DATE datatype, then we can do something like this:

 SELECT r.id AS room_id 
      , r.quantity - IFNULL(SUM(IF('2012-12-01' BETWEEN v.from AND v.until, v.quantity, 0)),0) AS `1`
      , r.quantity - IFNULL(SUM(IF('2012-12-02' BETWEEN v.from AND v.until, v.quantity, 0)),0) AS `2`
      , r.quantity - IFNULL(SUM(IF('2012-12-03' BETWEEN v.from AND v.until, v.quantity, 0)),0) AS `3`
      , ... 

   FROM room r
   LEFT
   JOIN room_reservation v
     ON v.room_id  = r.id
    AND v.until   >= '2012-12-01'
    AND v.from    <= '2012-12-31'
  GROUP BY r.id, r.quantity
  ORDER BY r.id

If those aren't DATE datatypes, and aren't strings in canonical YYYY-MM-DD format, then we can use the MySQL STR_TO_DATE function to convert to DATE. The SQL will just be a little messier.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • i did figure out query like this, i thought, there will be a query to generate date column given date range without generate it dynamically. in this question, https://stackoverflow.com/a/9296238 the query generated date. I wonder, if mysql able to make some kind of pivot table – user3392555 Oct 11 '18 at 19:32
  • There wasn't any indication in the question that you already had a query like this. It is helpful, when asking a question, to show what you have tried. The number of columns to be returned, the datatype and the name of each column must be specified in the SQL; this can't be dynamic within the context of a single SELECT statement. Certainly, there are other patterns that will return an equivalent result. It would be simpler to return the results on separate rows, row-wise rather than column wise. But if we need to the result returned as 31 columns, thenconditional aggregation is the way to go. – spencer7593 Oct 11 '18 at 19:45