SET SESSION sql_mode = '';
select i.item_code, bt1.`name` as name1 ,b1.barcode as barcode1,b1.pack_size as pack_size1,u1.uom_name as uom1
,bt2.`name` as name2 ,b2.barcode as barcode2,b2.pack_size as pack_size2,u2.uom_name  as uom2
,bt3.`name` as name3,b3.barcode as barcode3,b3.pack_size as pack_size3,u3.uom_name  as uom3
,bt4.`name` as name4,b4.barcode as barcode4,b4.pack_size as pack_size4,u4.uom_name  as uom4
from 0p4_item i

left join 0p4_item_barcode b1 on b1.item_id=i.item_id and b1.barcode_type_id=1
left join 0p4_barcode_type bt1 on bt1.barcode_type_id=b1.barcode_type_id
left join 0p4_uom u1 on u1.uom_id=b1.uom_id

left join 0p4_item_barcode b2 on b2.item_id=i.item_id and b2.barcode_type_id=4
left join 0p4_barcode_type bt2 on bt2.barcode_type_id=b2.barcode_type_id
left join 0p4_uom u2 on u2.uom_id=b2.uom_id

left join 0p4_item_barcode b3 on b3.item_id=i.item_id and b3.barcode_type_id=6
left join 0p4_barcode_type bt3 on bt3.barcode_type_id=b3.barcode_type_id
left join 0p4_uom u3 on u3.uom_id=b3.uom_id

left join 0p4_item_barcode b4 on b4.item_id=i.item_id and b4.barcode_type_id=9
left join 0p4_barcode_type bt4 on bt4.barcode_type_id=b4.barcode_type_id
left join 0p4_uom u4 on u4.uom_id=b4.uom_id

where b1.barcode_type_id=1
group by i.item_id
limit 20
