How to use a join clause by adding a column indicating the existence of a link

Question

How to use a join clause by adding a column indicating the existence of a link

Navigation

#1 by (0 votes)

0

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 owns it. To know if he has, it is enough that he has gained hours in the activity.

Of the two forms I tried, I was able to return only the emblems he had and all the emblems at once, but without distinguishing which of them he actually possessed. As an example of the filled-in Hours and Activities tables, 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 expected result example above, I show that the student has badge 1 because he has earned hours in the corresponding activity. However, it does not have the emblem 2, because it did not gain hours.

mysql join

asked by anonymous 16.02.2018 / 13:05

1 answer

PHP CURL sometimes returns empty result Regex to search for words

score 0 · Answer 1

Make a LEFT JOIN and put a if to check that b.id is null 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';