List records that are in a table and those that are not in the same table

Question

I have a points table and a users table. At points, I have an ID that references the users table user. What I need to do is return all users who have hit the point, along with those who haven’t hit the point. So I can see who hit and who didn’t hit point on any date.

The points table has the following data:

• date Date type (date the user hit the point)
• user (user id reference in User table)
• inada01 TIME type (time the user entered)
• out01 type TIME (time the user left)
• entrada02 TIME type (time the user entered)
• out02 type TIME (time the user left)

The user table has the following data: id, name

Answer 1

I will assume that a user who has entered 01 or entered 02 on the selected date has clocked in.

DECLARE @DATA DATE;
SET @DATA = '2016-05-09';



select
    nome,
    CASE WHEN Picou > 0 then 'Sim' else 'Não' end 'Picou'
from (
    select
        u.nome,
        SUM(CASE WHEN DATE(d.entrada01) = @DATA OR
        DATE(d.entrada02) THEN 1 ELSE 0 END) 'Picou'
    from user u
    left join dados d on
        u.id = d.usuarioid
    group by
        u.nome
) t

EDIT:

DECLARE @DATA DATE;
SET @DATA = '2016-05-09';

select
    u.nome,
    d.*
from user u
left join (
    select
        d.*
    from dados d on
    where
        DATE(d.entrada01) = @DATA OR
        DATE(d.entrada02) = @DATA
) d on
    u.id = d.usuarioid

Answer 2

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.

Answer 3

It works:

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 bateu_ponto
from 
    user left join pontospordia ponto on ponto.usuarioId = user.id
where 
    (dataPonto between '2016-05-07' and '2016-05-10')
    or dataPonto is null
group by 
    user.id, user.name, ponto.dataPonto, ponto.entrada01, 
    ponto.saida01, ponto.entrada02, ponto.saida02, bateu_ponto

Above is my original query plus OR of @Developer.

See it working in your sql fiddle: http://sqlfiddle.com/#! 9/332356/60.

The only thing is that searching for more than one date and showing the query date doesn’t help much to know when a user did not hit point, because if he did not hit he will not have a corresponding record in the beat table, so that will not show the date to him.

The solution is to query for a single date and display in the application the date used for the query instead of trying to get this date from the query result.