-- View: "v_item_list"

DROP VIEW v_item_list;

CREATE OR REPLACE VIEW v_item_list AS
SELECT
    basic.itemcd,
    basic.itemnm,
    basic.itemnmkana,
    basic.makercd,
    maker.makernm,
    basic.itemexp,
    basic.option01 as basic_option01,
    basic.option02 as basic_option02,
    basic.option03 as basic_option03,
    basic.option04 as basic_option04,
    basic.option05 as basic_option05,
    basic.option06 as basic_option06,
    basic.option07 as basic_option07,
    basic.option08 as basic_option08,
    basic.option09 as basic_option09,
    basic.option10 as basic_option10,
    basic.updcnt AS item_basic_updcnt,
    struct.structcd,
    struct.variationnm,
    struct.makeritemcd,
    struct.jancd,
    struct.salestartdate,
    struct.orderclosedate,
    struct.makerprice,
    struct.comment AS struct_comment,
    struct.option01 as struct_option01,
    struct.option02 as struct_option02,
    struct.option03 as struct_option03,
    struct.option04 as struct_option04,
    struct.option05 as struct_option05,
    struct.option06 as struct_option06,
    struct.option07 as struct_option07,
    struct.option08 as struct_option08,
    struct.option09 as struct_option09,
    struct.option10 as struct_option10,
    struct.updcnt AS item_struct_updcnt,
    web.releasedate,
    web.rankingrate,
    web.comment,
    web.updcnt AS item_web_attr_updcnt,
    s_item.sellercd,
    member.nicknm,
    (SELECT count(*) FROM t_image
      WHERE typediv = 1     -- 1:品目
        AND generalcd = basic.itemcd
        AND delflg=FALSE) AS image_count
FROM
    t_item_struct struct,
    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_seller_item s_item
              ON basic.itemcd = s_item.itemcd
                AND s_item.delflg = FALSE
    LEFT JOIN t_member_basic_attr member
              ON s_item.sellercd = member.membercd
                AND member.delflg = FALSE
                  
WHERE
        basic.itemcd = struct.itemcd
    AND basic.delflg = FALSE
    AND struct.delflg = FALSE
    AND web.delflg = FALSE;