Re: Alternative to a correlated query?

From: James Lorenzen <james_lorenzen_at_allianzlife.com>
Date: Wed, 27 Oct 1999 14:50:42 GMT
Message-ID: <7v73g0$pj$1@nnrp1.deja.com>

OK, I’ll try this and get rid of the correlated subquery. This is assumming that you only want to see the Accepted Partners. This works in 7.3.4

SELECT p.id "Partner ID", p.name "Partner Name", a.Status "Status", a.Apprvl_No "Approval Number"
FROM partner p,

     (SELECT Ptnr_ID, Status, Apprvl_No
      FROM approval
      WHERE (Ptnr_ID, Date_a) IN (SELECT Ptnr_ID, MAX(Date_a)
                                  FROM approval GROUP BY Ptnr_ID)
     ) a

WHERE p.ID = a.Ptnr_ID
  AND a.Status = 'Accepted'

HTH
    James
In article <38166b81.6502987_at_news.netvigator.com>,   shanx_at_removethis.shanx.com wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 27 1999 - 09:50:42 CDT