使用与嵌套选择和临时表的联合

Question

This is a follow-up question to the excellent answer given by @amdixon on this thread the other day.

Quick summary: I had this MySql table (simplified):

tbl_cards
ID  FROM        TO
--------------------------
1  2015-10-01  2015-10-08
2  2015-10-06  2015-10-12
3  2015-10-06  2015-10-15
4  ...

I needed a SELECT which checked every date between e.g. 2015-10-01 and 2015-12-31 and returned the dates where 3 (or an arbitrary number of) ID's overlap. Some dates won't have any records, while others may have a lot.

@amdixon supplied me with this answer, which was excactly what I needed (mind you, there is also a mySql view called 'digits' involved. Have a look at the thread above)

select @index := @index + 1 as `Index`, `Date`
from
(
select date_format(calendar.dy, '%Y-%m-%d') as `Date`
from
(
select date_add(date('2015-10-01'), interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day) as dy
from digits a2
cross join digits a1
cross join digits a0
where date_add('2015-10-01', interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day) <= date('2015-12-31')
order by date_add('2015-10-01', interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day)
) calendar
inner join tbl_cards t
on calendar.dy between t.`from` and t.`to`
group by calendar.dy
having count(calendar.dy) = 3
) dts
cross join ( select @index := -1 ) params
;

For my follow-up question, it might be necessary to give some further details. The tbl_cards table is used to store bookings that can span everything from one to ten dates. Hence the from and to fields.

Now one of my customers have asked me to make a shopping cart for this, meaning that each user can make more than one booking in one transaction. I want to make this work by creating a tbl_shoppingcart where bookings are temporarily stored for 30 minutes or so, until the user are done and have completed payment.

However, this makes the excellent query above obsolete, because calendar now needs to compare against both tbl_cards and tbl_shoppingcart simultaneously, and make sure that the total number of confirmed bookings + cart bookings for any given date doesn't exceed 3 (or an arbitrary number defined by my customer).

I'm sure there will be a UNION involved, but I'm really not sure where to put it since the HAVING COUNT clause should span both tables.

Here's the sqlfiddle supplied by @amdixon.

Answer 1

After twisting my head for about 24 hours, I was able to derive the right query from a similar post (however lot less complicated query) here at Stack Overflow:

select @index := @index + 1 as `Index`, `Date`
from
(
  select date_format(calendar.dy, '%Y-%m-%d') as `Date`
  from
  (
    select date_add(date('2015-10-01'), interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day) as dy
    from digits a2
    cross join digits a1
    cross join digits a0
    where date_add('2015-10-01', interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day) <= date('2015-12-31')
    order by date_add('2015-10-01', interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day)
  ) calendar
  inner join
  (
    SELECT `ID`, `from`, `to` FROM `tbl_cards`
    UNION
    SELECT `ID`, `from`, `to` FROM `tbl_shoppingcart`
  ) t
  on calendar.dy between t.`from` and t.`to`
  group by calendar.dy
  having count(calendar.dy) = 3
) dts
cross join ( select @index := -1 ) params
;

Obviously I didn't know enough about derived tables and subqueries, otherwise this would have been an easy customization. Seems like I've learned something new today as well :)