-- View: "v_label_list"

DROP VIEW v_label_list;

CREATE OR REPLACE VIEW v_label_list AS
SELECT
    mkr.makercd,
    mkr.makernm,
    mkr.makernmkana,
    lbl.labelcd,
    lbl.labelnm,
    lbl.labelnmkana,
    lbl.recommenddiv,
    lbl.updcnt,
    (SELECT COUNT(*)
        FROM t_image
        WHERE typediv = 5   -- 5:レーベル
            AND generalcd = lbl.makercd
            AND generalsubcd = lbl.labelcd
            AND imgsizecd = 'S'
            AND delflg = FALSE) AS img_count_s,
    (SELECT COUNT(*)
        FROM t_image
        WHERE typediv = 5   -- 5:レーベル
            AND generalcd = lbl.makercd
            AND generalsubcd = lbl.labelcd
            AND imgsizecd = 'M'
            AND delflg = FALSE) AS img_count_m,
    (SELECT COUNT(*)
        FROM t_image
        WHERE typediv = 5   -- 5:レーベル
            AND generalcd = lbl.makercd
            AND generalsubcd = lbl.labelcd
            AND imgsizecd = 'L'
            AND delflg = FALSE) AS img_count_l,
    (SELECT COUNT(*)
        FROM t_item_basic_attr
        WHERE makercd = lbl.makercd
            AND labelcd = lbl.labelcd) AS item_count
FROM t_label lbl
    INNER JOIN t_maker mkr
        ON mkr.makercd = lbl.makercd
            AND mkr.delflg = FALSE
WHERE lbl.delflg = FALSE
ORDER BY lbl.makercd, lbl.labelcd;