Can this be done in 1 query?

[nxm150]

SELECT FIELD1
FROM TABLE1 A,
TABLE2 B
WHERE A.FIELD1 = :WS-FIELD1
AND B.FIELD1 = A.FIELD1
AND A.FILED2 = 2
AND B.FIELD3 = 2
AND B.FIELD4 = 'N'

I want to add another WHERE clause to make sure WS-FIELD1 is not in another table (TABLE3).

Do I run my first query, then run a 2nd query or can I set up just 1 query?

 

[amorous]

Does this work??

SELECT A.FIELD1
FROM TABLE1 A,
TABLE2 B,
TABLE3 C,
WHERE A.FIELD1 = :WS-FIELD1
AND B.FIELD1 = A.FIELD1
AND A.FILED2 = 2
AND B.FIELD3 = 2
AND B.FIELD4 = 'N'
AND C.FIELD1 <> :WS-FIELD1

-VJ

 

[BrianTyler]

You need a sub query:

SELECT FIELD1
FROM TABLE1 A,
TABLE2 B
WHERE A.FIELD1 = :WS-FIELD1
AND B.FIELD1 = A.FIELD1
AND A.FILED2 = 2
AND B.FIELD3 = 2
AND B.FIELD4 = 'N'
and not exists
(select c.field3
from table3
where .....)