MySQL select boolean based on whether record exists in another table

case, codeigniter, full-outer-join, mysql, subquery

Solution

I realised I didn't go far enough with my experimentation, here is a finalised query which produces the results I wanted. Here's the result for anyone else with a similar question.

SELECT Entity.*,
          CASE WHEN UsersSavedEntity.user_id = '$user_id' 
               THEN 1 
               ELSE 0 
          END AS favourite
FROM Entity 
LEFT JOIN (SELECT * 
           FROM SavedEntity WHERE SavedEntity.user_id = '$user_id') 
           AS UsersSavedEntity 
          ON Entity.id = UsersSavedEntity.entity_id

Problem

Hi I have a table of entities which a user can select, I also have another table which contains information about a user's favourite entities. See: Table `Entity`: ``` id | ... ``` Table `SavedEntity`: ``` id | entity_id | user_id ``` I want the results to be like: ``` entity_id | ... | favourite ``` My question is how do I change my query which gets all of the entities and add an additional field is a `boolean` value of whether the entity is a favourite of the user? I've looked at using (or emulating!) a `FULL JOIN` but this seems unnecessarily complicated and I couldn't quite get it working, and I've looked at the `CASE` keyword but again I had no luck with it. This many-to-many table structure is fairly standard I believe so I'm sure there's a standard way of getting the information required, could anyone help me figure out what I'm missing? N.B. I'm using CodeIgniter and the active record stuff for my queries, but I can work around vanilla SQL.

Original source