/*คำสั่งเรียก 06.เจ้าพนักงานทันตสาธารณสุข ที่บันทึก provider ถูกต้อง */

set @provider = '06';

SELECT

doctor.`code`,doctor.pname,doctor.fname,doctor.lname,doctor.cid,

doctor.provider_type_code,pvt.provider_type_name,doctor.sex,

doctor.birth_date,doctor.start_date,doctor.finish_date,doctor.active

FROM

doctor

LEFT JOIN provider_type as pvt on pvt.provider_type_code = doctor.provider_type_code

WHERE doctor.provider_type_code =@provider

and doctor.pname is not null and doctor.fname is not null and doctor.lname is not null and length(cid)=13 and doctor.sex in ('1','2')

and doctor.birth_date < curdate() and doctor.start_date is not null

#-----------------------