-- View: "v_maker_list"

DROP VIEW v_maker_list;

CREATE OR REPLACE VIEW v_maker_list AS
SELECT
    mkr.makercd,
    mkr.makernm,
    mkr.makernmkana,
    mkr.comment,
    mkr.recommenddiv,
    mkr.shopdiv,
    mkr.updcnt,
    (SELECT COUNT(*)
        FROM t_image
        WHERE typediv = 4   -- 4:メーカー
            AND generalcd = mkr.makercd
            AND imgsizecd = 'S'
            AND delflg = FALSE) AS img_count_s,
    (SELECT COUNT(*)
        FROM t_image
        WHERE typediv = 4   -- 4:メーカー
            AND generalcd = mkr.makercd
            AND imgsizecd = 'M'
            AND delflg = FALSE) AS img_count_m,
    (SELECT COUNT(*)
        FROM t_image
        WHERE typediv = 4   -- 4:メーカー
            AND generalcd = mkr.makercd
            AND imgsizecd = 'L'
            AND delflg = FALSE) AS img_count_l,
    (SELECT COUNT(*)
        FROM t_item_basic_attr
        WHERE makercd = mkr.makercd) AS item_count
FROM t_maker mkr
WHERE mkr.delflg = FALSE
ORDER BY mkr.makercd;