เขียน sql เพื่อดูว่า เด็กแต่ละคนฉีดวัคซีนอะไรไปบ้างแล้ว อย่างนี้ได้หรือเปล่าครับ

[Guitar-hero]

ผมต้องการดูว่า เด็แต่ละคนฉีคซีนอะไรไปบ้างแล้ว เขียน 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

[nicespeed]

ลองใช้คำสั่งชุดนี้ดูนะค่ะ
select p.cid,concat(p.pname,p.fname,"  ",p.lname) as ptname
,p.mother_name,p.birthdate,p.age_y,p.age_m,p.current_age ,p.cid,h.address,h.road,v.village_moo,
v.village_name,t.full_name as full_address_name,pv.vaccine_name,l.vaccine_date,l.vaccine_note
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
left outer join person_vaccine_list l on l.person_id=p.person_id
left outer join person_vaccine pv on pv.person_vaccine_id=l.person_vaccine_id
where  v.village_id="'ใส่รหัสชุมชน'" and p.age_y between "0" and "5" and trim(p.cid)<>""
order by p.cid

[มนตรี บอยรักยุ้ยคนเดียว]

ผมต้องการดูว่า เด็แต่ละคนฉีคซีนอะไรไปบ้างแล้ว เขียน 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

ใช้ได้นะครับ แก้แค่
WHERE age_y< 6 and v.village_moo in ("ใส่หมู่ที่ต้องการ","02")