CREATE DEFINER=`monty`@`%` PROCEDURE `sp_doctor_search_patient_portal`(
	IN search_string VARCHAR ( 255 ))
BEGIN
	SELECT
		doctors.doctor_id,
		doctors.id,
		doctor_name,
		prnt_name,
		qualification,
		dept_id,
		specialty_id,
		dob,
		gender,
		phone_no,
		reg_no,
		service_rates.rate,
		speciality_name
	FROM
		doctors,
		doctor_clinic_schedule,
		service_rates,
		doctor_speciality 
	WHERE
		doctors.id = doctor_clinic_schedule.doctor_id 
			AND service_rates.id = visit_charge 
			AND doctor_speciality.id = doctors.specialty_id 
			AND doctor_clinic_schedule.is_active = 'Y'
		AND 
		(
			doctor_name SOUNDS LIKE CONCAT('%', search_string, '%') OR doctor_name LIKE CONCAT('%', search_string, '%')
			OR
			qualification SOUNDS LIKE CONCAT('%', search_string, '%') OR qualification LIKE CONCAT('%', search_string, '%')
			OR
			speciality_name SOUNDS LIKE CONCAT('%', search_string, '%') OR speciality_name LIKE CONCAT('%', search_string, '%')
		)
	GROUP BY
	doctors.id;
END