-- View: "v_cast_list"

DROP VIEW v_cast_list;

CREATE OR REPLACE VIEW v_cast_list AS
SELECT
    cst.castcd,
    cst.castnm,
    cst.castnmkana,
    cst.castdiv,
    name.name AS castdivnm,
    cst.recommenddiv,
    cst.comment,
    cst.debutdate,
    cst.birthplacecd,
    pref.prefnm AS birthplacenm,
    cst.birthdate,
    cst.shopdiv,
    cst.updcnt,
    CASE WHEN EXISTS(SELECT *  FROM t_image
        WHERE typediv = 3   -- 3:キャスト
          AND generalcd = cst.castcd
          AND imgsizecd = 'S'
          AND delflg = FALSE) THEN 1
     ELSE 0
    END AS img_count_s,
    CASE WHEN EXISTS(SELECT *  FROM t_image
        WHERE typediv = 3   -- 3:キャスト
          AND generalcd = cst.castcd
          AND imgsizecd = 'M'
          AND delflg = FALSE) THEN 1
     ELSE 0
    END AS img_count_m,
    0 AS img_count_l,
    (SELECT COUNT(*)
        FROM t_cast_item
        WHERE castcd = cst.castcd) AS item_count

FROM t_cast cst
    LEFT JOIN t_name name
        ON name.namediv = 6 -- 6:キャスト
            AND name.namecd = cst.castdiv
    LEFT JOIN t_pref pref
        ON pref.prefcd = cst.birthplacecd
WHERE cst.delflg = FALSE
ORDER BY cst.castcd;