SQL Query To Establish Consecutive "Years"

From: Chris Battles <ctbattles_at_hotmail.com>
Date: 15 Nov 2002 16:55:52 -0800
Message-ID: <7b78a601.0211151655.7b7e1c68@posting.google.com>

I'm trying to construct a query that will find activity for a particular organization that has taken place in three consecutive "years" in the row (in this case the year is just a number and not a true date data type.) For example, to be valid we should find payments in 2002, 2001, and 2000.

You usually join each of the tables by their primary keys as follows:

SELECT name, req_amt, budgyear, pay_amt
FROM Organization O, Request R, Payment P WHERE O.org_id = R.org_id AND
R.req_id = P.req_id
ORDER BY name, budgyer DESC;

Each organization can have multiple requests and each request can have multiple payments.

Here are the tables:

Organization:

---

ORG_ID NUMBER(10) NOT NULL (PRIMARY_KEY) NAME VARCHAR2(100) Request:

---

REQ_ID    NUMBER(10)    NOT NULL (PRIMARY_KEY)
ORG_ID    NUMBER(10)    NOT NULL (FOREIGN_KEY)
REQ_AMT   NUMBER(13,2)

Payment:

---

PAY_ID    NUMBER(10)    NOT NULL (PRIMARY_KEY)
REQ_ID    NUMBER(10)    NOT NULL (FOREIGN_KEY)
PAY_AMT   NUMBER(13,2)

BUDGYEAR NUMBER(4) Any help would be much appreciated. Thanks! Received on Fri Nov 15 2002 - 18:55:52 CST