I have two tables: one is Tour and other is ReservedTour. The columns of the tables are:
Tour | ReservedTour ===========|============= id | id city | Tid capacity | number timeout | . . | . . | . . | ========== |======
I write an SQL statement such as
select *, (select sum(rt.`number`) from ReservedTour as rt where rt.`Tid`=t.id GROUP BY rt.`Tid`) as total from Tour as t where City = 'alahom' and '1400-12-13' <= t.`timeout` and 4 < t.`Capacity`- total;
but this has an error — the total is not correct.
How can I correct this?
select t.*, sum(rt.`number`) as total from Tour as t join ReservedTour as rt on rt.`Tid`=t.id where City = 'alahom' and t.`timeout`=> '1400-12-13' and t.`Capacity`-total > 4 GROUP BY t.id