CODEWITHSUNDEEP
sqloracle-database
cooldude
cooldude

Reputation: 125

ORA-DB - How to retrieve data that does not exists in a table

I have the following table with the some sample data:

|| Filename || receiving_site || originating_site ||
| fileA | siteA | HQ |
| fileA | siteA | siteA |
| fileB | siteA | HQ |
| fileA | siteB | HQ |
| fileA | siteB | siteB |
| fileB | siteB | HQ |
| fileB | siteB | siteB |

The table works in such way:

  1. For each of the "Filename", if "originating_site" is not the same as "receiving_site", it means that the file has been sent to "receiving_site" but has not been acknowledged received yet.
  2. For each of the "Filename", if "originating_site" is the same as "receiving_site", it means the file has been sent and received by the "receiving_site".

My task is to list out all the "Filename" per "receiving_site" that has been sent, but not received yet by the "receiving_site". For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.

How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.

Upvotes: 0

Views: 78

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16915

(select filename, receiving_site
   from yourTable)
minus
(select filename, receiving_site
   from yourTable
where receiving_site = originating_site)

all the data without the ones that were received

Upvotes: 1

Related Questions