How to use a Join clause by adding a column indicating the existence of a connection

Question

I have two tables

Horas (id, aluno_id, atividade_id)

and

Atividades (id, emblema_id)

In addition to returning all the badges, I need to return a column that tells me if that student has it. To know if he has, just that he has earned hours in activity.

Of the two ways that I tried, I was able to return only the emblems that he had and all the emblems at once, but without discriminating which one he actually had. As an example of the tables Hours and Activities filled, respectively, follows:

id | aluno_id | atividade_id
1  |    1     | 1


id | emblema_id
1  | 1
2  | 2

The expected result should be:

emblema_id | possui
    1      | Sim
    2      | Não
         ...

In the above expected result example, I demonstrate that the student has badge 1 because he has earned hours in the corresponding activity. However, it does not have the emblem 2, as it did not gain hours.

Answer 1

Make a LEFT JOIN and put a if to check whether the b.id is void or not:

SELECT a.`emblema_id`, IF(b.`id` IS NOT NULL, "SIM", "NÃO") AS 'possui' FROM `Atividades` a
LEFT JOIN `Horas` b ON a.`id` = b.`atividade_id` AND b.`aluno_id` = 1
GROUP BY b.`aluno_id`, b.`atividade_id`;