MySQL select boolean based on whether record exists in another table

Question

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.

Answer 1

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