Mysql Query - Two tables and date range

Question

I have two tables, one for the technicians' register and one for the tasks. It’s a 1->N relation.

Table 1 (TECNICO):

cd_tecnico | nome

Table 2 (TASK)

cd_tarefa | cd_tecnico | dt_inicio | dt_fim

I need two filters to check on any date (user informed) who is and who is not on a task.

1 - List who is on a task on the date informed. Here it works OK.

Select * from TECNICO tec
inner join TAREFA ta ON 
ta.cd_tecnico = tec.cd_tecnico
and 
(ta.dt_inicio <='2016-03-10' and ta.dt_conclusao>='2016-03-10') 

2 - List who is not on a task on the informed date.

Here is my question in the best way to do. I tried a select from another select but it got too slow and didn’t work direct.

What would be the best solution in this case?

Answer 1

You can use the left join to bring all the TECHNICAL, and a Case to verify who has the task or who does not.

declare @TECNICO table
(
  cd_tecnico int,
  nome varchar (30)
)

declare @TAREFA table
(
  cd_tecnico int,
  cd_tarefa int,
  nome varchar (30),
  dt_inicio date,
  dt_conclusao date
)


insert into @TECNICO values
(1, 'João'),
(2, 'Pedro'),
(3, 'Maria'),
(4, 'Carlos'),
(5, 'Antonio')


insert into @TAREFA values
(1, 1 ,'Correção banco', '2016-03-10', '2016-03-10' ),
(2, 1 ,'Correção app', '2016-03-10', '2016-03-10' ),
(3, 1 ,'Correção grid', '2016-03-11', '2016-03-11' ),
(5, 1 ,'Correção banco', '2016-03-10', '2016-03-10' )

Select *, case when ta.cd_tecnico is not null then 'Tem tarefa'  else 'não tem tarefa' end as Tarefa
from @TECNICO tec
left join @TAREFA ta 
ON ta.cd_tecnico = tec.cd_tecnico
and (ta.dt_inicio <='2016-03-10' and ta.dt_conclusao >= '2016-03-10') 

Answer 2

A complete answer for you to reflect (I did not put the conditions of the Where clause, it is up to you).

A traditional way is to use sub-queries for what you need this will work in most Dbms and not only in Mysql. Of course, this is a solution if the DBMS does not have available resource of Case When, used in the other answer by the friend @Dotnet.

It is worth remembering that it is important to measure the performance of the consultation, because when using sub-consultations, the consumption can be high.

CREATE TABLE TECNICO(
    CODIGO INTEGER NOT NULL PRIMARY KEY,
    NOME VARCHAR(500) NOT NULL
);

CREATE TABLE TAREFA(
    CODIGO INTEGER NOT NULL PRIMARY KEY,
    DESCRICAO VARCHAR(500) NOT NULL, 
    DATA_INICIO DATETIME NOT NULL, 
    DATA_FIM DATETIME NOT NULL,
    TECNICO INTEGER NOT NULL,
    CONSTRAINT FK_TAREFA_TECNICO FOREIGN KEY (TECNICO)
    REFERENCES TECNICO(CODIGO)
);

INSERT INTO TECNICO VALUES (1,'MATEUS');
INSERT INTO TECNICO VALUES (2,'JOSÉ');
INSERT INTO TECNICO VALUES (3,'CARLOS');
INSERT INTO TECNICO VALUES (4,'PATRÍCIA');

INSERT INTO TAREFA VALUES (100, 'CRIAR EJBs',
STR_TO_DATE('1/6/2016 8:06:26 AM', '%d/%m/%Y %r'),
STR_TO_DATE('30/6/2016 8:06:26 AM', '%d/%m/%Y %r'),1);

INSERT INTO TAREFA VALUES (101, 'Criar testes unitarios',
STR_TO_DATE('15/6/2016 8:06:26 AM', '%d/%m/%Y %r'),
STR_TO_DATE('30/6/2016 8:06:26 AM', '%d/%m/%Y %r'),1);

INSERT INTO TAREFA VALUES (102, 'Gerar build',
STR_TO_DATE('1/6/2016 8:06:26 AM', '%d/%m/%Y %r'),
STR_TO_DATE('2/6/2016 8:06:26 AM', '%d/%m/%Y %r'),2);

-- Aqui temos uma junção interna (só trará as correspondências que existem)
SELECT * FROM TECNICO te
inner join TAREFA ta ON te.CODIGO = ta.TECNICO;

-- Trará quem tem ou não tarefa (juntos - à esquerda)
SELECT * FROM TECNICO te
left outer join TAREFA ta ON te.CODIGO = ta.TECNICO;

-- Só os técnicos que não possuem tarefa. A query interna retorna os tecnicos que possuem tarefas e a externas aqueles que estão fora do conjunto. bastaria você acescentar a clausula where no select interno (subconsulta).
SELECT * FROM TECNICO te where te.codigo NOT IN (SELECT te.codigo FROM TECNICO te
inner join TAREFA ta ON te.CODIGO = ta.TECNICO)