I have a query that gets data based on multiple tables
X(id, state)
has all employees with state enabled/disabled
Y(id, state)
has all groups (of employees) with state enabled/disabled
A(id)
scientists/scientist groups with access
B(id, type)
has employee ids and groups ids. Each with type employee/group.
so A.a
will be either in X
or in Y
select A.id, B.type
from A
inner join B on A.id = B.id
I don't need any columns from X or Y. I need to remove from select those which have X.state="disabled"
Looks like you just need two NOT EXISTS
.
select
a.id,
b.type
from A a
inner join B b on a.id = b.id
and not exists (select 1
from x
where x.id = a.id
and x.state = 'disabled'
) and not exists (select 1
from y
where y.id = a.id
and y.state = 'disabled'
);