Query in the same table

Asked

Viewed 247 times

1

I have a movement table that has worker’s record and related events,

inserir a descrição da imagem aqui

wanted a query that returns only the records that have exactly the events E1,E2 and E3, in the case of the above example the records would be 001 and 003

sql query table

  • Use the WHERE clause. A short tutorial: https://www.w3schools.com/sql/sql_where.asp

  • I understand, but in case it would have to show the workers who own exactly the three events only

2 answers

2


You did not specify which database you are using, but you can do this in several ways. I used the example Mysql and made 2 ways to do 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');

Upshot:

registro
001
003

Using URGE 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;

Upshot:

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

I put in the Sqlfiddle

  • Worked buddy, thank you

  • @Please tick the [Tour] tab as an answer. If you have any questions, please visit to see how

1

Follows 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.

Browser other questions tagged sql query table

You are not signed in. Login or sign up in order to post.