Search code examples
sqlunixsqlplus

Select rows where a value on table x is 1 greater than the same value on table y

I need to create a report of all the rows where a value in table (x) is 1 greater than another value in table (y).

For example, I want to select all rows from TABLE X where the 'Total' is 1 greater than the 'Sum' in TABLE Y. So here I want to select ONLY Dai's record:

TABLE X:

Name Total
Dai 1001
Cam 1001

TABLE Y:

Name Sum
Dai 1000
Cam 1001

I am running this SQL in an older version of SQL*Plus so any newer methods probably won't work.

Thanks in advance!

Solution

  • I think the solution could be like this:

    select * 
from X join Y on X.Total = Y.Sum + 1 and X.Name = Y.Name;