ผมต้องการดูว่า เด็แต่ละคนฉีคซีนอะไรไปบ้างแล้ว เขียน sql แบบด้านล่างนี้ถูกหรือเปล่าครับ ช่วยแนะนำหน่อยครับ แล้วถ้าต้องการดูเฉพาะ บางหมู่บ้าน ต้องเพิ่มคำสั่งอย่างไรไปบ้างครับ
SELECT p.person_id,
p.cid,/*
concat(p.pname,p.fname,' ',p.lname) as ptname ,
concat(p.age_y," ?? ",p.age_m,"
??") as p_age,p.current_age ,
p.cid, h.address,h.road,v.village_moo,v.village_name,t.full_name as full_address_name,
*/
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=5) as bcg_vaccine ,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=6) as Hbv1,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=12) as Opv1,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=9) as DPT1,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=7) as Hbv2,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=13) as Opv2,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=10) as DPT2,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=14) as OPV3,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=11) as DPT3,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=8) as HBV3,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=15) as MMR1,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=16) as Measle,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=18) as OPV4,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=17) as DPT4,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=19) as JE1,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=20) as JE2,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=21) as JE3,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=23) as OPV5,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=22) as DPT5
FROM person p
LEFT OUTER JOIN house h ON h.house_id = p.house_id
LEFT OUTER JOIN village v on v.village_id = p.village_id
LEFT OUTER JOIN thaiaddress t on t.addressid = v.address_id
WHERE age_y< 6 and v.village_moo<>0
ORDER BY village_moo,p.age_y
แสดงกระทู้