-- View: "v_item_tag"

DROP VIEW v_item_tag;

CREATE VIEW v_item_tag AS
SELECT
    i.itemcd,
    t.tagnm,
    max(t.createdate) as createdate,
    max(t.referdate) as referdate,
    COUNT(t.tagnm) as tagcount
FROM t_tag t
    INNER JOIN t_item_basic_attr i
    ON i.itemcd = t.itemcd
    INNER JOIN t_member_basic_attr m
    ON t.usercd = m.membercd AND m.memberstate = 1

WHERE
    i.delflg = FALSE
GROUP BY i.itemcd, t.tagnm;