CODEWITHSUNDEEP
sqloracle-database
Martin Čuka
Martin Čuka

Reputation: 18488

Select Table A minus Table B where condition consists of two columns

I have two tables. Table A and table B.

I would like to select everything from table A which is NOT in table B. Sounds easy the catch is I need to select it based on two values (two columns) revision AND casetype. Something like this.

select a.revision, a.casetype from A a
minus
select b.revision, b.casetype from B b;

The problem is I won't get back ID from table A.

Is it possible to select whole table A minus table B where conditions consist of two columns ? I would like to stick to SQL (no PL/SQL)

I also tried to write something like query below but I guess I can't do it since I need to check revision AND casetype altogether

select * from A a where a.casetype IN (select...) and a.revision IN (select...)

Any idea how to work around ? Thanks

Upvotes: 1

Views: 2955

Answers (3)

Ramkumar Sambandam
Ramkumar Sambandam

Reputation: 557

except should work

select a.revision, a.casetype from A a except select b.revision, b.casetype from B b;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270493

Oracle supports tuples, so if you wanted you could do:

select a.*
from a
where (a.revision, a.casetype) in (select a.revision, a.casetype from A a
                                   minus
                                   select b.revision, b.casetype from B b
                                  );

I would normally go for not exists, but this is the solution that builds on what you have already done.

Upvotes: 3

John Humphreys
John Humphreys

Reputation: 39314

Sure, I believe a basic not exists check should work.

select a.id, a.revision, a.casetype
from A a
where not exists (
    select 1
    from B
    where revision = a.revision and casetype = a.casetype
);

Upvotes: 4

Related Questions