CODEWITHSUNDEEP
sqloracle-database
Mikeb530
Mikeb530

Reputation: 15

ignore column in minus statement

I am unable to find a solution to this problem.

There are two tables to minus from each other.

Select columnA, columnB, columnC from tableA
minus
Select columnA, columnB, columnC from tableB

I need the data from tableA.columnC, however it will never match to tableB.columnC, so the minus statement will return both records.

Is there a way to ignore columnC from the minus statement but still return the results?

-Thanks

P.S. A little background on this problem. I have a transaction table that list date of purchase, billed amount, item purchased (sometimes listed, sometimes not), account number of purchaser, and a unique identifier for the purchase.

This table holds both purchases and reversals. So if someone bought a item for 10.00 and then returns the item there is a -10.00 for that. In some cases a person will buy, return, and buy that item again within the same day. This data is from a third party.

Unfortunately a sum does not work in this case I need a line by line detail of all purchases that have not been reversed.

I was able to get the correct data by following this posts advice and using a: Prevent Oracle minus statement from removing duplicates

However there is more information that I need from this table than just the information that I am using to minus off the returns.

This is why i would like help with the above statement.

Upvotes: 1

Views: 725

Answers (2)

DCR
DCR

Reputation: 15665

Try this:

select cola,colb,colc from tableA as A
inner join
(select cola,colb from tableA
 minus
 select cola,colb from tableB) as B
 on a.cola = b.cola
 and a.colb = b.colb;

Upvotes: 1

Jayvee
Jayvee

Reputation: 10875

something on these lines should work:

Select columnA, columnB, columnC from tableA a
where not exists
(Select 1 from tableB b where a.columnA=b.columnA and a.columnB=b.columnB)

Upvotes: 2

Related Questions