Keyur Vaidya Keyur Vaidya - 3 years ago 186
SQL Question

SQL Query, how to omit specific data?

I am writing a SQL Query that returns a list of Cost Center's that are closed. This is known by a 'C' in the TE_INACTIVE Column.

The issue: Some Cost Centers were moved to a different company, so there is a duplicate of 3 cost centers that are both closed and open. Since this cost center was closed and re-opened, I do not want to read it in anymore since the new one doesn't have a 'C'. How can I do this?

In other words, if one of the duplicate Cost Center's doesn't have a 'C' in it, then I want to skip it.

Here is (part of) my original query and a screenshot of what I mean.

select distinct TE_COST_CENTER, TE_INACTIVE from ABCS_TABLE_E where
TE_COST_CENTER = '38M'


Click this to view picture!

Answer Source

You should use the "NOT EXISTS" operator:

select distinct TE_COST_CENTER, TE_INACTIVE from ABCS_TABLE_E t1
where TE_INACTIVE = 'C'
and not exists (select * from ABCS_TABLE_E t2 where t2.TE_COST_CENTER = t1.TE_COST_CENTER and t2.TE_INACTIVE <>'C')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download