根据另一个表的内容在MySQL中选择布尔值 [英] Selecting boolean in MySQL based on contents of another table

问题描述

我有下表:

Name: Posts

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

和

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'中是否有记录.

I'd like to return all the values from the first table and a boolean stating whether there is a record in the other table WHERE Type='delete'.

第二个表中的Target字段对应于第一个表中的Id.

The Target field in the second table corresponds to the Id in the first table.

我有以下查询(不起作用).一方面，它会为Action表中的每个对应行返回该行的实例，即使对于除"delete"之外的其他动作也是如此.

I have the following query (which doesn't work). For one thing it returns an instance of the row for each corresponding row in the Action table, even for actions other than 'delete'.

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'以外的任何其他表返回第一张表中该行的多个副本.

I do not want multiple copies of the row in the first table returned for Type='like' or any others apart from Type='delete'.

我从此样本获得的理想结果:

My desired result from this sample:

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

我希望这是合理的，对于如此冗长的问题/解释，我深表歉意.

I hope this makes sense and my apologies for such a lengthy question / explanation.

推荐答案

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

这篇关于根据另一个表的内容在MySQL中选择布尔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！