Selecting boolean in MySQL base don another table's contents

Sunday, May 4, 2014

I have the following tables:

Id Creator Title Body Tags Time


Id Creator Type Target

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'.

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

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` WHERE `Action`.`Type` != 'like' ORDER BY `Id` DESC;

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