在MySQL中根据另一个表的内容选择布尔值

我有以下表格：

Name: Posts

Id Creator Title Body Tags Time
1  1       Test  Test Test 123456789

and

Name: Action

Id Creator Type Target
1  1       like 1
2  3       like 1
3  2       like 1
4  1       delete 1

我想返回第一个表中的所有值和一个布尔值，说明另一个表中是否有记录WHERE Type='delete'.

The Target第二个表中的字段对应于Id在第一个表中。

我有以下查询（不起作用）。一方面，它为操作表中的每个对应行返回该行的实例，即使对于“删除”以外的操作也是如此。

SELECT `Posts`.*, IF(`Action`.`Type`='deleted',1,0) AS "Deleted" 
FROM `Posts` JOIN 
     `Action` ON `Action`.`Target` = `Posts`.`Id` 
ORDER BY `Id` DESC;

我不希望返回第一个表中的行的多个副本Type='like'或除此之外的任何其他人Type='delete'.

我从这个样本中得到的期望结果：

Id Creator Title Body Tags Time Deleted
1  1       Test  Test Test 123456789 1

我希望这是有道理的，并对如此冗长的问题/解释表示歉意。

SELECT Posts.*, IF(Action.Id IS NULL,0,1) as Deleted 
FROM Posts 
LEFT JOIN Action 
ON Action.Target = Posts.Id and Type='delete'

如果你不关心性能

SELECT Posts.*,
       exists
  (SELECT 1
   FROM Action
   WHERE Target = Posts.Id
     AND TYPE='delete') AS Deleted
FROM Posts