CODEWITHSUNDEEP
sqloracle-database
Саят Оразов
Саят Оразов

Reputation: 55

How to exclude rows from table?

I have a query, but I need to exclude certain rows. My query:

SELECT eo, ac, tc, ac_05
  FROM eng_effect e, eng_tc t, task_c c
 WHERE e.eo = t.eo
   AND t.tc = c.tc
   AND e.ac <> c.ac

Which is fine, but I need to exclude combinations of eo, tc which are in eng_tc table.

For example, my query returns this values:

EO AC TC AC_05
1A-CHECK7 KEB 1A - 767 KEA
1A-CHECK7 KEA 1A - 767 KEC
1A-CHECK7 KEC 1A - 767 KEB

But this rows should not be in the intended result, since in eng_tc table corresponding tc

for eo = '1A-CHECK7' is '1A - 767'.

Upvotes: 0

Views: 140

Answers (1)

GWR
GWR

Reputation: 209

I would write the query like this:

select
    eo,
    ac,
    tc,
    ac_05
from
    eng_effect e
    inner join task_c c on c.tc = t.tc and c.ac <> e.ac
    left join eng_tc t on t.eo = e.eo and t.tc = e.tc
where
    t.eo is null
;

I rewrote your query for readability because you were using rather old-fashioned sql. I created a left join instead of an inner join for t. Then I added the WHERE clause to filter the result set for any record where there is no corresponding record in t, using one of the key columns - could also have used tc.

Upvotes: 1

Related Questions