SQL select ids with columns that has a least two tags

Question

I am working on job related data and wanted to do some analysis.

I have two tables a Job table with job_id and an Application table with app_id,job_id,tag.

I want to select all job_ids that have an application with at least one tag with the name -> 'hired' AND at least one tag with the name 'not hired'. May I know how do i do this in Postgresql?

Job Table

job_id
1
2
3

Application table

app_id	job_id	tag
1	1	hired
2	1	not hired
3	2	hired
4	2	hired
5	3	not hired
6	3	not hired

In this case i want my output to be

job_id
1

job_id 2 and 3 is not included because it does not have at least 1 hired and at least 1 not hired in the tags.

Answer 1

You can aggregate all tags into an array, then test if at least the two arrays you are after are included in that:

select job_id
from application
group by job_id
having array_agg(tag) @> array['hired', 'not hired']

If you need columns from the job table as well, you can join the result to it:

select j.*
from job j
join ( 
  select job_id 
  from application
  group by job_id
  having array_agg(tag) @> array['hired', 'not hired']
) a on a.job_id = j.job_id

Online example

Answer 2

You can use CTE to achieve it (result here)

with x as (select distinct job_id,tag from application)
select job_id
from x
group by job_id
having count(*) > 1

or you can use subqueries

select job_id
from (select distinct job_id,tag from application) x
group by job_id
having count(*) > 1;