SELECT `product`.*,
`price`.`price` AS `price`,
`price`.`currency_id` AS `currency_id`,
`currency`.`code` AS `currency`,
`category`.`label` AS `category`,
`subcategory`.`label` AS `subcategory`,
`brand`.`label` AS `brand`,
`measurement`.`label` AS `measurement`,
`stock`.`quantity` AS `quantity`,
`stock`.`measurement_id` AS `measurement_id`
FROM `core_sivir`.`sivir_products` AS `product`
LEFT JOIN `core_sivir`.`sivir_products_price` AS `price` ON `price`.`product_id` = `product`.`relative`
LEFT JOIN `core_sivir`.`core_sivir_currency` AS `currency` ON `price`.`currency_id` = `currency`.`id`
LEFT JOIN `core_sivir`.`sivir_catalog_category` AS `category` ON `category`.`catalog_code` = 'CATEGORY' AND `category`.`catalog_code_dep` = 0 AND `category`.`catalog_id_dep` = 0 AND `category`.`global_branch_office_id` = `product`.`global_branch_office_id`
LEFT JOIN `core_sivir`.`sivir_catalog_subcategory` AS `subcategory` ON `subcategory`.`catalog_code` = 'CATEGORY' AND `subcategory`.`catalog_code_dep` = 'CATEGORY' AND `subcategory`.`catalog_id_dep` = `product`.`category_id` AND `subcategory`.`global_branch_office_id` = `product`.`global_branch_office_id`
LEFT JOIN `core_sivir`.`sivir_catalog_brand` AS `brand` ON `brand`.`catalog_code` = 'BRAND' AND `brand`.`catalog_code_dep` = 0 AND `brand`.`catalog_id_dep` = 0 AND `brand`.`global_branch_office_id` = `product`.`global_branch_office_id`
LEFT JOIN `core_sivir`.`sivir_catalog_measurement` AS `measurement` ON `measurement`.`catalog_code` = 'MEASUREMENT' AND `measurement`.`catalog_code_dep` = 0 AND `measurement`.`catalog_id_dep` = 0 AND `measurement`.`global_branch_office_id` = `product`.`global_branch_office_id`
LEFT JOIN `core_sivir`.`sivir_products_stock` AS `stock` ON `product`.`relative` = `stock`.`product_id` AND `stock`.`measurement_id` = `measurement`.`value`
WHERE `product`.`status` = 'A'
