sqlsql-serverinner-join

Is there any way to filter based on two tables


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"


Solution

  • 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'
    );