-- View: "v_series_list"

DROP VIEW v_series_list;

CREATE OR REPLACE VIEW v_series_list AS
SELECT
    series.makercd,
    series.seriescd,
    series.seriesnm,
    series.seriesnmkana,
    series.recommenddiv,
    series.updcnt,
    maker.makernm,
    maker.makernmkana,
    maker.shopdiv,
    (SELECT COUNT(*)
        FROM t_item_basic_attr
        WHERE makercd = series.makercd
            AND seriescd = series.seriescd) AS item_count


FROM t_series series
    INNER JOIN t_maker maker
        ON series.makercd = maker.makercd
            AND maker.delflg = FALSE
WHERE series.delflg = FALSE
ORDER BY maker.makercd, series.seriescd;