In the Caffé solution, it was only necessary to include a clause in the Where data is null:
select
user.id, user.nome, ponto.data, ponto.entrada01,
ponto.saida01, ponto.entrada02, ponto.saida02
(case when ponto.data is not null then 'sim' else 'não') as bateu_ponto
from user left join ponto on ponto.usuarioId = user.id
where (data is null or (data between data1 and data2))
group by user.id, user.nome, ponto.data, ponto.entrada01,
ponto.saida01, ponto.entrada02, ponto.saida02, bateu_ponto
Users who have not hit the point do not have the date, for this reason, you also need to consider that the date is null or in the range you inform to users who have point.
======================CORRECT SOLUTION=====================
The above solution does not answer. If the user has a point at a date other than the one reported in Where, that is, it does not meet the Where clauses because it is not null and is different from the filter date.
The correct solution was to create a sub query only with the points of the day you want to query, with this table I made a left Join with user, now all users are being returned even if there are points for other dates. Follow the new query:
select user.id, user.name, ponto.dataPonto,
ponto.entrada01, ponto.saida01,
ponto.entrada02, ponto.saida02,
case when (ponto.dataPonto is not null) then 'sim' else 'não' end as bateu_ponto
from user left join
(select *
from pontospordia p
where p.dataPonto between '2016-05-07' and '2016-05-10 23:59:59') ponto on ponto.usuarioId = user.id
group by user.id,
user.name,
ponto.dataPonto,
ponto.entrada01,
ponto.saida01,
ponto.entrada02,
ponto.saida02
I hope I’ve helped.
Something like this doesn’t solve?
select * from users inner join pontos on pontos.user = user.id
– user28595
@Diegof This way I can only see who hit, but I need to return, too, who did not hit the point on each date.
– Marcelo Augusto
Then show the structure of your table, the question is not clear.
– user28595
I changed the code to show the attributes.
– Marcelo Augusto
If it’s to show all users, one
users LEFT JOIN pontos
must solve.– Bacco