On 21 Mar, 14:43, Anony <y...@no.com> wrote:
> On 21 Mar 2007 07:23:54 -0700, "Captain Paralytic"
>
>
>
>
>
> <paul_laut...@yahoo.com> wrote:
> >On 21 Mar, 14:21, Anony <y...@no.com> wrote:
> >> Hello there, I am trying to return all accounts whos *LATEST*
> >> arrangement on that account was NOT met, ie Arrangement_Met=0
>
> >> here are the tables in question
>
> >> TABLE Accounts
> >> (
> >> Account_ID
> >> Account_Number
> >> ... <more fields>
>
> >> }
>
> >> TABLE Arrangement
> >> {
> >> Arrangement_ID
> >> Account_ID
> >> Arrangement_Met
> >> Arrangement_Date
> >> Arrangement_Time
> >> ... <more fields>
> >> FOREIGN KEY (Account_ID)
>
> >> }
>
> >> I can return returns all accounts who have an arrangement that is NOT
> >> met no
> >> problem *but* if the last arrangement is met, and they have older
> >> arrangements that
> >> are not met it will still return that account... what I need is some
> >> nifty sql that will
> >> only return accounts whos most recently created arrangment (ordered by
> >> date and
> >> time) has not been met, ie Arrangement.Arrangement_Met=0 for all
> >> accounts ...
>
> >> This returns all accounts who have an arrangement that has not been
> >> met
>
> >> Select * from Accounts
> >> left outer join Arrangement on (Accounts.Account_ID =
> >> Arrangement.Account_ID) WHERE Arrangement.Arrangement_Met=0
>
> >> what I need is some nifty sql that will only return accounts whos
> >> **last / most recently added** arrangment has *not* been met, ie
> >> Arrangement.Arrangement_Met=0,
> >> regardless of older arrangements! ...
>
> >> Any help whatso ever anyone can give me would be amazing! Thanks in
> >> advance for
> >> taking the time to look at my problem...
>
> >Search on this NG for "strawberry query". This question gets asked a
> >lot and the answer is always the strawberry query.
>
> thank for your help
>
> I have had a look and a cannot find an exact "strawbery query" and
> what I do find is very hard to understand....
>
> This query isnt that hard, I am sure, I just need some kind of example
> using somthing similar and I am good...- Hide quoted text -
>
> - Show quoted text -

You won't find "strawbery query", but you will find "strawberry
query". Search on Google using the quotes.

You will need to join Arrangement to itself using aliases, your join
criteria will have to contain something along the lines of
(a1.Arrangement_Date > a2.Arrangement_Date OR a1.Arrangement_Date =
a2.Arrangement_Date AND a1.Arrangement_Time > a2.Arrangement_Time)
And the WHERE clause will need to test for a1.Arrangement_Date or
a1.Arrangement_Time being NULL. It is complicate by the fact that you
have separate date and time fields rather than a single datetime field.