CODEWITHSUNDEEP
sqljoinoracle11g
sayhaha
sayhaha

Reputation: 789

pl/sql exclusion join with two condition

I want to do exclude join if row has (same tool and same recipe) when I run following code

select a.tool, a.recipe
from dual a
where a.tool NOT IN (select b.tool from daul2 b)
and a.recipe NOT IN (select b.recipe from dual2 b)

It, first, filters tool then recipe, but I wish the program check it at the same time.

Is there a way to check two column at the same time?

Upvotes: 0

Views: 2101

Answers (1)

Justin Cave
Justin Cave

Reputation: 231781

I'm not completely sure that I understand what you're asking. My guess is that you want either

SELECT a.tool, a.recipe
  FROM table1 a
 WHERE (a.tool, a.recipe) NOT IN (SELECT b.tool, b.recipe
                                    FROM table2 b)

or

SELECT a.tool, a.recipe
  FROM table1 a
 WHERE NOT EXISTS( SELECT 1
                     FROM table2 b
                    WHERE a.tool = b.tool 
                      AND a.recipe = b.recipe )

If that's not what you want, could you post some sample data and explain what you are trying to exclude and include?

Upvotes: 3

Related Questions