-- View: "v_item_list_compact"

DROP VIEW v_item_list_compact;

CREATE OR REPLACE VIEW v_item_list_compact AS
SELECT
    basic.itemcd,
    basic.itemnm,
    basic.makercd,
    maker.makernm,
    basic.labelcd,
    label.labelnm,
    basic.seriescd,
    series.seriesnm,
    basic.iteminfo,
    basic.shopdiv,
    (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,
    COALESCE(r.count, 0) AS count,
    COALESCE(r.refercount, 0) AS refercount
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 v_item_ranking r
              ON r.itemcd = basic.itemcd
    LEFT JOIN t_series series
              ON basic.makercd = series.makercd AND basic.seriescd = series.seriescd
WHERE
        basic.delflg = FALSE
    AND web.delflg = FALSE
;