สอบถามจากคำสั่ง SQL

[autobotz]

จากคำสั่ง SQL ด้านล่างถ้าจะแก้ไขให้ถูกควรแก้ตรงไหนครับ   
รบกวนทุก ๆ ท่านด้วยนะครับ   




select v.vstdate,v.hn,concat(p.pname,p.fname," ",p.lname)as ptname,op.bmi,op.bps,op.bpd,

(select lo.lab_order_result from lab_order lo
where lo.lab_items_name_ref like '%Hb A1C') as HbA1C,

(select lo.lab_order_result from lab_order lo
where lo.lab_items_name_ref like 'Glucose') as Glucose


from vn_stat v
left outer join opdscreen op on op.vn = v.vn
left outer join patient p on p.hn = v.hn
left outer join lab_head lh on lh.vn = v.vn
left outer join lab_order lo on lo.lab_order_number = lh.lab_order_number
where v.vstdate between '2012-01-01' and '2012-01-31'
and (lo.lab_items_name_ref like '%Hb A1C' or lo.lab_items_name_ref like 'Glucose')
and lh.vn <> ""
and lo.lab_order_result <>""
and op.bmi <>""
and op.bps <>""
and op.bpd <>""
and v.age_y >= '65'
group by ptname
order by lo.lab_items_name_ref

[Kumpol Khonburi]

ไม่รู้ว่าต้องการแนวนี้หรือเปล่าก็ลองเอาไปปรับดูนะครับ

select v.vstdate,v.hn,concat(p.pname,p.fname," ",p.lname)as ptname,op.bmi,op.bps,op.bpd,
lo_g.lab_order_result as Glucose,lo_h.lab_order_result as HbA1C
from vn_stat v
left outer join opdscreen op on op.vn = v.vn
left outer join patient p on p.hn = v.hn
left outer join lab_head lh on lh.vn = v.vn
left outer join lab_order lo_g on lo_g.lab_order_number = lh.lab_order_number and lo_g.lab_items_name_ref like '%Glucose%'
left outer join lab_order lo_h on lo_h.lab_order_number = lh.lab_order_number and ((lo_h.lab_items_name_ref like '%Hb A1C%') or (lo_h.lab_items_name_ref like '%HbA1c%'))
where v.vstdate between '2012-01-01' and '2012-01-31'
and lh.vn <> ""
and lo_g.lab_order_result <>""
and lo_h.lab_order_result <>""
and op.bmi <>""
and op.bps <>""
and op.bpd <>""
and v.age_y >= '65'
group by ptname

[autobotz]

ไม่รู้ว่าต้องการแนวนี้หรือเปล่าก็ลองเอาไปปรับดูนะครับ

select v.vstdate,v.hn,concat(p.pname,p.fname," ",p.lname)as ptname,op.bmi,op.bps,op.bpd,
lo_g.lab_order_result as Glucose,lo_h.lab_order_result as HbA1C
from vn_stat v
left outer join opdscreen op on op.vn = v.vn
left outer join patient p on p.hn = v.hn
left outer join lab_head lh on lh.vn = v.vn
left outer join lab_order lo_g on lo_g.lab_order_number = lh.lab_order_number and lo_g.lab_items_name_ref like '%Glucose%'
left outer join lab_order lo_h on lo_h.lab_order_number = lh.lab_order_number and ((lo_h.lab_items_name_ref like '%Hb A1C%') or (lo_h.lab_items_name_ref like '%HbA1c%'))
where v.vstdate between '2012-01-01' and '2012-01-31'
and lh.vn <> ""
and lo_g.lab_order_result <>""
and lo_h.lab_order_result <>""
and op.bmi <>""
and op.bps <>""
and op.bpd <>""
and v.age_y >= '65'
group by ptname

ได้ตามที่คาดหวังครับ ขอบคุณมากครับ   

[udomchok]

ใช้แค่ like "%a1c%" ก็ได้ครับ ไม่ต้องใช้ 2 เงื่อนไข