Posted by clare at snyder.on.ca on 03/18/06 21:24

On Sat, 18 Mar 2006 16:05:54 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:


>It appears that the data you have is very messy, with various pieces
>of information built into the policy number. Since I don't know the
>tables in detail, and only have your narrative to work from, this
>is a bit of guesswork:
>
> SELECT c.lastname, c.email, p.polno, p.polexpdate
> FROM customer c
> JOIN (SELECT polid, polexpdate = MAX(polexpdate)
> FROM policies
> WHERE polno like '1234%'
> GROUP BY polid) AS p1 ON p1.polid = c.polid
> JOIN policies p ON p.polid = p1.polid
> AND p.polexpdate = p1.polexpdate
> WHERE p.polexpdate BETWEEN '20060301' AND '20060331'
> AND c.email IS NOT NULL
>
>First, I have assumed that the polid does not change over renewals, but
>that is plain guess on my part.
>
>The inner SELECT gives the most recent expiration date for each polid,
>and then I filter to see only those with expiration in March 2006.
>
The "outer select" works if written:

Select c.lastname, c.email, p.polno, p.polexpdate
FROM afw_customer c, afw_basicpolinfo p

If we get the "inner select" working, the JOIN will likely work as
well.

*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

[Back to original message]