find a records which exists for 2 values only

Question

I have Table structure/Data set is like this.

Emp_id  Expense_amt_dollar  Dept
1111    100                 Marketing    
1111    75                  Finance
1111    25                  IT
2222    100                 Marketing
3333    50                  Finance
4444    30                  Marketing
4444    70                  Finance
5555    200                 IT

O/P I am looking for Emp expense in 2 dept only

Emp_id  Expense_amt_dollar  Dept
1111    100                 Marketing
1111    75                  Finance
4444    30                  Marketing
4444    70                  Finance

Emp which having records for these 2 dept only. Records should have for both dept.

Answer 1

With EXISTS:

select t.* from tablename t
where t.dept in ('Finance', 'Marketing')
and exists (
  select 1 from tablename
  where emp_id = t.emp_id and dept in ('Finance', 'Marketing') and dept <> t.dept
)

or with a CTE:

with cte as (
  select t.* from tablename t
  where t.dept in ('Finance', 'Marketing')
)
select c.* from cte c
where exists (
  select 1 from cte 
  where emp_id = c.emp_id and dept <> c.dept
)

See the demo.
Results:

> EMP_ID | EXPENSE_AMT_DOLLAR | DEPT     
> -----: | -----------------: | :--------
>   1111 |                 75 | Finance  
>   1111 |                100 | Marketing
>   4444 |                 70 | Finance  
>   4444 |                 30 | Marketing