
drop view if EXISTS v_com_contract;
CREATE
VIEW `v_com_contract`AS
SELECT DISTINCT
	t.comContractId,
	contractType,
	contractStatus,
	contractName,
	comFileNo,
	comParty,
	firstPartyName,
	firstPartyTel,
	secondPartyName,
	secondPartyTel,
	effectStartDate,
	effectEndDate,
	contractPeriod,
	agreementSignDate,
	perId,
	t.comId,
	t.remark,
	(
        select
          GROUP_CONCAT(ct.taskName)
        FROM com_task_contract ctc
        INNER JOIN com_task ct on ct.comTaskId=ctc.comTaskId
        WHERE t.comContractId = ctc.comContractId
      ) as taskName
FROM
	com_contract t
WHERE
	t.isDelete = '0';