SQL select ids with columns that has a least two tags-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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;