CREATE VIEW vw_cross_class as SELECT T1.class_id AS class_id1, T2.class_id AS class_id2, T1.classcategory_id AS classcategory_id1, T2.classcategory_id AS classcategory_id2, T1.name AS name1, T2.name AS name2, T1.rank AS rank1, T2.rank AS rank2 FROM dtb_classcategory AS T1, dtb_classcategory AS T2 ; CREATE VIEW vw_cross_products_class AS SELECT T1.class_id1, T1.class_id2, T1.classcategory_id1, T1.classcategory_id2, T2.product_id, T1.name1, T1.name2, T2.product_code, T2.stock, T2.price01, T2.price02, T1.rank1, T1.rank2 FROM vw_cross_class AS T1 LEFT JOIN dtb_products_class AS T2 ON T1.classcategory_id1 = T2.classcategory_id1 AND T1.classcategory_id2 = T2.classcategory_id2 ; CREATE VIEW vw_products_nonclass AS SELECT * FROM dtb_products AS T1 LEFT JOIN ( SELECT product_id AS product_id_sub, product_code, price01, price02, stock, stock_unlimited, classcategory_id1, classcategory_id2 FROM dtb_products_class WHERE classcategory_id1 = 0 AND classcategory_id2 = 0 ) AS T2 ON T1.product_id = T2.product_id_sub ; CREATE VIEW vw_products_allclass_detail AS SELECT dtb_products.product_id, dtb_products."name", dtb_products.deliv_fee, dtb_products.sale_limit, dtb_products.maker_id, dtb_products.rank, dtb_products.status, dtb_products.product_flag, dtb_products.point_rate, dtb_products.comment1, dtb_products.comment2, dtb_products.comment3, dtb_products.comment4, dtb_products.comment5, dtb_products.comment6, dtb_products.note, dtb_products.file1, dtb_products.file2, dtb_products.file3, dtb_products.file4, dtb_products.file5, dtb_products.file6, dtb_products.main_list_comment, dtb_products.main_list_image, dtb_products.main_comment, dtb_products.main_image, dtb_products.main_large_image, dtb_products.sub_title1, dtb_products.sub_comment1, dtb_products.sub_image1, dtb_products.sub_large_image1, dtb_products.sub_title2, dtb_products.sub_comment2, dtb_products.sub_image2, dtb_products.sub_large_image2, dtb_products.sub_title3, dtb_products.sub_comment3, dtb_products.sub_image3, dtb_products.sub_large_image3, dtb_products.sub_title4, dtb_products.sub_comment4, dtb_products.sub_image4, dtb_products.sub_large_image4, dtb_products.sub_title5, dtb_products.sub_comment5, dtb_products.sub_image5, dtb_products.sub_large_image5, dtb_products.sub_title6, dtb_products.sub_comment6, dtb_products.sub_image6, dtb_products.sub_large_image6, dtb_products.del_flg, dtb_products.creator_id, dtb_products.create_date, dtb_products.update_date, dtb_products.deliv_date_id, dtb_products.down, dtb_products.down_filename, dtb_products.down_realfilename, T4.product_code_min, T4.product_code_max, T4.price01_min, T4.price01_max, T4.price02_min, T4.price02_max, T4.stock_min, T4.stock_max, T4.stock_unlimited_min, T4.stock_unlimited_max, T4.class_count FROM dtb_products LEFT JOIN ( SELECT product_id, 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, COUNT(*) as class_count FROM dtb_products_class GROUP BY product_id ) AS T4 ON dtb_products.product_id = T4.product_id ; CREATE VIEW vw_products_allclass AS SELECT alldtl.*, dtb_category.rank AS category_rank, T2.category_id, T2.rank AS product_rank FROM vw_products_allclass_detail AS alldtl LEFT JOIN dtb_product_categories AS T2 ON alldtl.product_id = T2.product_id LEFT JOIN dtb_category ON T2.category_id = dtb_category.category_id ; CREATE VIEW vw_product_class AS SELECT * FROM (SELECT T3.product_class_id, T3.product_id AS product_id_sub, classcategory_id1, classcategory_id2, T3.rank AS rank1, T4.rank AS rank2, T3.class_id AS class_id1, T4.class_id AS class_id2, stock, price01, price02, stock_unlimited, product_code FROM (dtb_products_class AS T1 LEFT JOIN dtb_classcategory AS T2 ON T1.classcategory_id1 = T2.classcategory_id) AS T3 LEFT JOIN dtb_classcategory AS T4 ON T3.classcategory_id2 = T4.classcategory_id) AS T5 LEFT JOIN dtb_products AS T6 ON product_id_sub = T6.product_id; CREATE VIEW vw_category_count AS SELECT T1.category_id, T1.category_name, T1.parent_category_id, T1.level, T1.rank, T2.product_count FROM dtb_category AS T1 LEFT JOIN dtb_category_total_count AS T2 ON T1.category_id = T2.category_id ; CREATE VIEW vw_download_class as SELECT p.product_id AS product_id, p.down_realfilename AS down_realfilename, p.down_filename AS down_filename, od.order_id AS order_id, o.customer_id AS customer_id, o.commit_date AS commit_date, o.status AS status FROM dtb_products p, dtb_order_detail od, dtb_order o WHERE p.product_id = od.product_id AND od.order_id = o.order_id;