How to write query in frappe to generate get attendance api

we are to collect enroll no from if shift schedule employee and employee list employee_id is same then only get enroll no
Note-: we are create api on get shift schedule(table) but we want to get enroll no from employee(table).



select  employee_name,
employee_id,
case when (1=1)
then (select enroll_number from `tabEmployee`
where `tabEmployee`.name = `tabShift Schedule`.employee_id)
else 0
end as enroll_number,
leave_type, attendance_date, company, store, shift_time, start_time, end_time, amended_from\
    FROM `tabShift Schedule`
    WHERE attendance_date='{0}'""".format(attendance_date),as_dict=1)