(SELECT T1.product_id,
           product_code_min,
           product_code_max,
           price01_min,
           price01_max,
           price02_min,
           price02_max,
           stock_min,
           stock_max,
           stock_unlimited_min,
           stock_unlimited_max,
           del_flg,
           status,
           name,
           comment1,
           comment2,
           comment3,
           main_list_comment,
           main_image,
           main_list_image,
           product_flag,
           deliv_date_id,
           sale_limit,
           point_rate,
           sale_unlimited,
           create_date,
           deliv_fee,
           rank
           ,(SELECT rank AS category_rank
               FROM dtb_category AS T4
              WHERE T1.category_id = T4.category_id) as category_rank
           ,(SELECT category_id AS sub_category_id 
               FROM dtb_category T4 
              WHERE T1.category_id = T4.category_id) as category_id
      FROM (SELECT T0.product_id,
                   T0.del_flg,
                   T0.status,
                   T0.name,
                   T0.comment1,
                   T0.comment2,
                   T0.comment3,
                   T0.main_list_comment,
                   T0.main_image,
                   T0.main_list_image,
                   T0.product_flag,
                   T0.deliv_date_id,
                   T0.sale_limit,
                   T0.point_rate,
                   T0.sale_unlimited,
                   T0.create_date,
                   T0.deliv_fee,
                   T00.category_id,
                   T00.rank
              FROM dtb_products AS T0
         LEFT JOIN dtb_product_categories AS T00
             USING (product_id)) AS T1 
RIGHT JOIN (SELECT product_id as product_id_sub,
                   MIN(product_code) AS product_code_min,
                   MAX(product_code) AS product_code_max,
                   MIN(price01) AS price01_min,
                   MAX(price01) AS price01_max,
                   MIN(price02) AS price02_min,
                   MAX(price02) AS price02_max,
                   MIN(stock) AS stock_min,
                   MAX(stock) AS stock_max,
                   MIN(stock_unlimited) AS stock_unlimited_min,
                   MAX(stock_unlimited) AS stock_unlimited_max
              FROM dtb_products_class GROUP BY product_id) AS T2 
                ON T1.product_id = T2.product_id_sub

select
  p.product_id,
  p.del_flg,
  p.status,
  p.name,
  p.comment1,
  p.comment2,
  p.comment3,
  p.main_list_comment,
  p.main_image,
  p.main_list_image,
  p.product_flag,
  p.deliv_date_id,
  p.sale_limit,
  p.point_rate,
  p.sale_unlimited,
  p.create_date,
  p.deliv_fee,
  pca.category_id,
  pclg.product_code_min,
  pclg.product_code_max,
  pclg.price01_min,
  pclg.price01_max,
  pclg.price02_min,
  pclg.price02_max,
  pclg.stock_min,
  pclg.stock_max,
  pclg.stock_unlimited_min,
  pclg.stock_unlimited_max
from dtb_products as p
left outer join dtb_product_categories as pca on pca.product_id = p.product_id
left outer join dtb_category as c on c.category_id = pca.category_id
right outer join (
  select
    pcl.product_id,
    min(pcl.product_code) as product_code_min,
    max(pcl.product_code) as product_code_max,
    min(pcl.price01) as price01_min,
    max(pcl.price01) as price01_max,
    min(pcl.price02) as price02_min,
    max(pcl.price02) as price02_max,
    min(pcl.stock) as stock_min,
    max(pcl.stock) as stock_max,
    min(pcl.stock_unlimited) as stock_unlimited_min,
    max(pcl.stock_unlimited) as stock_unlimited_max
  from dtb_products_class as pcl
  group by pcl.product_id
) as pclg on pclg.product_id = p.product_id