-- View: "v_item_basic_list"

DROP VIEW v_item_basic_list;

CREATE OR REPLACE VIEW v_item_basic_list AS
SELECT
    basic.itemcd,
    basic.itemnm,
    basic.makercd,
    maker.makernm,
    basic.labelcd,
    label.labelnm,
    basic.seriescd,
    series.seriesnm,
    basic.iteminfo,
    basic.shopdiv,
    basic.updcnt AS item_basic_updcnt,
    (SELECT min(salestartdate) FROM t_item_struct
      WHERE basic.itemcd = t_item_struct.itemcd
        AND basic.delflg=FALSE AND t_item_struct.delflg=FALSE) AS salestartdate,
    web.releasedate,
    web.comment,
    web.updcnt AS item_web_attr_updcnt

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_label label
              ON basic.makercd = label.makercd AND basic.labelcd = label.labelcd
    LEFT JOIN t_series series
              ON basic.makercd = series.makercd AND basic.seriescd = series.seriescd
    WHERE
        basic.delflg = FALSE
    AND web.delflg = FALSE
;
