BMS-HOSxP Community
HOSxP => HOSxP PCU => ข้อความที่เริ่มโดย: numkum ที่ มกราคม 18, 2013, 12:08:56 PM
-
8) 8) ที่ รพ.สต.ต้องการรายละเอียดการรับวัคซีนของเด็กในพื้นที่ ลองใช้คำสั่ง SQL ได้ประมาณนี้ครับ แต่ต้องการรวม 2 ฟิลด์นี้ ต้องใช้คำสั่งอะไรครับ รบกวนด้วยครับ :-* :-*
SELECT p.person_id,p.cid,p.pname,p.fname,p.lname,p.birthdate,p.age_y,p.age_m,p.age_d,
p.father_name,p.mother_name,
h.address,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_elsewhere where person_id=p.person_id and person_vaccine_id=5) as bcg_vaccine_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=6) as Hbv1_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=12) as Opv1_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=9) as DPT1_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=7) as Hbv2_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=13) as Opv2_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=10) as DPT2_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=14) as OPV3_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=11) as DPT3_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=8) as HBV3_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=15) as MMR1_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=16) as Measle_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=18) as OPV4_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=17) as DPT4_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=19) as JE1_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=20) as JE2_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=21) as JE3_1,
(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_elsewhere where person_id=p.person_id and person_vaccine_id=23) as OPV5_1,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=22) as DPT5,
(select vaccine_date from person_vaccine_elsewhere where person_id=p.person_id and person_vaccine_id=22) as DPT5_1,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=49) as ROTA1,
(select vaccine_date from person_vaccine_elsewhere where person_id=p.person_id and person_vaccine_id=49) as ROTA1_1,
(select vaccine_date from person_vaccine_list where person_id=p.person_id and person_vaccine_id=50) as ROTA2,
(select vaccine_date from person_vaccine_elsewhere where person_id=p.person_id and person_vaccine_id=50) as ROTA2_1
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 p.birthdate between "2005-12-31" and "2012-02-21"
and v.village_moo<>0
ORDER BY village_moo,p.birthdate
-
concat(fied1,fied2) ??