How to fetch another doctype field value in sql script

Frappe HR
7

Please check the example:

SELECT
    att.employee AS "Employee",
    att.employee_name AS "Employee Name",
    DATE(att.time) AS "Date",
    TIMEDIFF(MAX(att.time), MIN(att.time)) AS "Total Time",
    emp.department AS "Department"
FROM
    `tabEmployee Checkin` AS att
JOIN
    `tabEmployee` AS emp ON att.employee = emp.name
WHERE
    emp.department = 'Sales'  -- Example: filter where employee's department is 'Sales'
GROUP BY
    att.employee, DATE(att.time)

Reference: