Adil Irshad Adil Irshad - 10 days ago 7
SQL Question

MySQL Left join with cross join query issue

I have 2 tables please see the link and current running query
http://sqlfiddle.com/#!9/40d9d/2

Issues I am facing

1. taking much time to execute

2. I want if in between date range date 'x' have no information in tbl_appoitmens table then much show the results with zero but all AppointmentTimeID in tbl_appointmentschedule_details.

3. I want to verify these 3 new columns

AppointmentTypeID
,
AppointemntStatus
and
AvailableInMarket
from
tbl_appointments
.

4.
ScheduleID
from
tbl_appointmentschedule_details


Query output speed really does matter we might be selecting like 2 - 3 years records at once.


results I want show be something like


AppointmentTimeID AppointmentDate NoOfApplicants
22 2015-10-16 2
23 2015-10-16 4
24 2015-10-16 5
25 2015-10-16 2
26 2015-10-16 2
22 2015-10-17 5
23 2015-10-17 2
24 2015-10-17 2
25 2015-10-17 2
26 2015-10-17 2
22 2015-10-18 0
23 2015-10-18 0
24 2015-10-18 0
25 2015-10-18 0
26 2015-10-18 0

Answer

Try this:

select t4.AppointmentTimeID, t4.AppointmentDate,ifnull(t5.NumberOfApplicants,0)
from
    (select distinct t2.AppointmentTimeID, t1.AppointmentDate
  from tbl_appointmentschedule_details t2 join (select t.AppointmentDate from (
SELECT adddate('2015-10-16', @rownum := @rownum + 1) as 'AppointmentDate' FROM tbl_appointments
JOIN (SELECT @rownum := -1) r
LIMIT 31
) t 
where t.AppointmentDate between '2015-10-16' and '2015-10-18') t1)  t4
left join 
    (SELECT t2.AppointmentTimeID,t1.AppointmentDate,sum(t1.NumberOfApplicants) as 'NumberOfApplicants'
    FROM tbl_appointmentschedule_details t2
    LEFT JOIN tbl_appointments t1 on t2.AppointmentTimeID=t1.AppointmentTimeID
    WHERE t1.AppointmentStatus='Pending' AND t1.AvailableInMarket=0
    GROUP BY t2.AppointmentTimeID,t1.AppointmentDate) t5 
on t4.AppointmentDate=t5.AppointmentDate and t4.AppointmentTimeID=t5.AppointmentTimeID
having t4.AppointmentTimeID!=0
order by t4.AppointmentDate,t4.AppointmentTimeID;