-- View: "v_item_basic2_list"

DROP VIEW v_item_basic2_list;

CREATE OR REPLACE VIEW v_item_basic2_list AS
SELECT
    basic.itemcd,
    basic.itemnm,
    basic.itemnmkana,
    basic.makercd,
    maker.makernm,
    basic.updcnt AS item_basic_updcnt,
    struct.salestartdate,
    struct.orderclosedate,
    web.releasedate,
    web.rankingrate,
    web.comment,
    web.updcnt AS item_web_attr_updcnt,
    COALESCE(s2.exhibitcount, 0) AS sellcount,
    COALESCE(s1.sellingprice, 0) AS sellingprice,
    COALESCE(s3.reserveflg, 0) AS reserveflg,
    struct.variationnm, 
    struct.makeritemcd, 
    struct.jancd, 
    struct.structcd,
    struct.makerprice
FROM
    t_item_basic_attr basic
    LEFT JOIN t_item_web_attr web
              ON basic.itemcd = web.itemcd
    LEFT JOIN t_maker maker
              ON basic.makercd = maker.makercd
    LEFT JOIN t_item_struct struct ON basic.itemcd = struct.itemcd
    LEFT JOIN (SELECT s.itemcd, min(s.sellingprice) as sellingprice
                        FROM v_stock_list s
                        WHERE s.exhibitcount > 0 AND s.stockstate = 3
                          AND ((sellercd IS NOT NULL AND sellernm IS NOT NULL AND memberstate=1) OR (sellercd IS NULL))
                        GROUP BY s.itemcd) s1
              ON s1.itemcd = basic.itemcd
    LEFT JOIN (SELECT s.itemcd, count(distinct s.stocktitlecd) as exhibitcount
                        FROM v_stock_list s
                        WHERE s.exhibitcount > 0 AND s.stockstate = 3
                          AND ((sellercd IS NOT NULL AND sellernm IS NOT NULL AND memberstate=1) OR (sellercd IS NULL))
                        GROUP BY s.itemcd) s2
              ON s2.itemcd = basic.itemcd
    LEFT JOIN (SELECT s.itemcd, min(s.reserveflg) as reserveflg
                        FROM v_stock_list s WHERE s.reserveflg=1 GROUP BY s.itemcd) s3
              ON s3.itemcd = basic.itemcd
WHERE
        basic.delflg = FALSE AND web.delflg = FALSE
;