Filtering results with Postgres selects

Asked

Viewed 31 times

0

I have the following table:

results

idRes(int)  | idEmp(int)    | data(varchar[8]) | resultado(text)
------------------------------------------------------------------
1           | 1             | 20200220         | (xml em texto)
2           | 1             | 20200221         | (xml em texto)
3           | 2             | 20200220         | (xml em texto)

I’m trying to find the company ids (in this case, idEmp = 1) that got results on 20/02/2020 (date = 20200220) E 21/02/2020 (date = 20200221)

I’m a little lost in how to do it. I know that

select idEmp Where data in ('20200220' and '20200221')

does not work, but I do not know how to proceed or correct.

postgresql

1 answer

3


You can use a subquery:

SELECT * FROM resultados a WHERE a.data = '20200220' AND 
EXISTS (SELECT 1 FROM resultados b WHERE b.idEmp = a.idEmp AND b.data = '20200221');

A question: if it is a date for which reason you do not declare the date field as DATE?

  • We didn’t have the idea to create the field as date or timestamp, and in this format we found it easier to sort the date (20200221 comes before 20200220, etc.). I’ll test your suggestion.

  • A form select idEmp Where data in ('20200220','20200221') group by idEmp having Count(distinct data) = 2

Browser other questions tagged postgresql

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