Query in same table

Question

Query in same table

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I have a movement table that has the worker's record and the events related to it,

I wanted a query that would return only the records that have exactly the events e1, e2 and e3, in case of the example above the records would be 001 and 003

sql query tabela

asked by anonymous 25.05.2018 / 23:21

2 answers

1

Here's an alternative:

SELECT registro FROM trabalhador 
WHERE registro IN (SELECT registro FROM trabalhador WHERE evento = 'e1')
AND registro IN (SELECT registro FROM trabalhador WHERE evento = 'e2')
AND registro IN (SELECT registro FROM trabalhador WHERE evento = 'e3') 
GROUP BY registro

Hug.

26.05.2018 / 00:11

Get last id or date inserted into a mysql table How to mount a Basic VirtualHost to rewrite URLs in my project in Xampp htdocs

score 1 · Accepted Answer

You have not specified which database you are using, but you can do this in several ways. I used Mysql as an example and did 2 forms for this:

Using EXISTS :

select distinct
m.registro
from movimento m
where 
exists (select 1 from movimento x where x.registro = m.registro and x.evento = 'e1')
and
exists (select 1 from movimento x where x.registro = m.registro and x.evento = 'e2')
and
exists (select 1 from movimento x where x.registro = m.registro and x.evento = 'e3');

Result:

registro
001
003

Using INSTR and GROUP_CONCAT :

select distinct
m.registro,
GROUP_CONCAT(m.evento) as eventos
from movimento m
group by m.registro
having 
INSTR(GROUP_CONCAT(m.evento),'e1') > 0
and 
INSTR(GROUP_CONCAT(m.evento),'e2') > 0
and 
INSTR(GROUP_CONCAT(m.evento),'e3') > 0;

Result:

registro    eventos
001         e1,e2,e3
003         e1,e2,e3

I put it in SQLFiddle