Re: Alternative to a correlated query?

From: Shashank Tripathi <shanx_at_shanx.com>
Date: Wed, 27 Oct 1999 03:28:20 GMT
Message-ID: <38166b81.6502987@news.netvigator.com>

HI Thomas,

Thanks for the suggestion, but I wish it were that simple! :)

I have consideredthe GROUP BY clause before, but my problem is that I am retrieving data from 8 tables in a single query (so there is a problem of JOINs).

The limitation of a GROUP BY is that we need to include ALL the non-group SELECT variables in the GROUP BY clause too. I think I gave an over-simplified example in my earlier post, which didnt really present my problem.

Lemme try to re-explain: Suppose I have two tables: Approval, and Partner.

Before a seller is entered into the PARTNER table, he has to apply and the result of this is kept into APPROVAL table. An individual can apply more than once (so he could be rejected once, but approved the next time). Given this, we have the following:

PARTNER TABLE: ID Name

---

001		Shanx
002		Thomas

APPROVAL TABLE: Appr_id Ptnr_ID Date Status Apprvl_No

---

123 001 1999/10/10 Rejected
223
124 002 1999/10/10 Accepted
224
125 001 1999/10/19 Accepted
300

So you see, the two partners: Shanx and Thomas remain the same in the partner table. Shanx is rejected on 10 Oct, and then accepted on 19 Oct. Thomas is accepted on Oct 10.

I wish to show the ID, Name and the status for ONLY THE RECENTMOST approval application.

So my resultset should be:

Partner ID Partner Name Status Approval Number

---

001		Shanx			Accepted		300
002		Thomas		Accepted	 	224

Now the issue with this is that if I use a straight SELECT clause with a join in the where condition, then the GROUP BY condition should contain the other details as well (not just partner ID). If I use a subquery, I'd need at least two nested subqueries, which again affects performance.

How would you achieve the above result?

Thanks for your patience and time.

Best regards
Shanx Received on Tue Oct 26 1999 - 22:28:20 CDT