Mysql query - Two tables and date range

Navigation
1

I have two tables, one for the technician registry and one for the tasks. It is a relation 1 -> N.

Table 1 (TECHNICAL): 

cd_tecnico | nome

Table 2 (TASK) 

cd_tarefa | cd_tecnico | dt_inicio | dt_fim

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

1 - List who is on a task on the given date. Here it is working 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 in a task on the given date.

Here's my question on the best way to do it. I tried a select from another select but it got too slow and did not work right.

What would be the best solution in this case?

    
asked by anonymous 01.06.2016 / 16:08

2 answers

1

You can use left join to bring all TECHNICAL , and a Case to check who has the task. 

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')
    
01.06.2016 / 16:30
1

A complete answer for you to reflect (I did not put the conditions of the where clause, it is at your discretion).

A traditional way is to use sub-queries for what you need this will work on most DBMSs and not just on MySQL. Of course, this is a solution if the DBMS does not have an available Case When feature, used in the other response by friend @DotNet.

It is important to remember that it is important to measure query performance, because when using subqueries, 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)
    
01.06.2016 / 16:45
Failed to connect to github.com port 443: Connection timed out Function empty returns true when value is 0