SEO

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...

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.

On 21 Mar 2007 07:23:54 -0700, "Captain Paralytic"
<paul_lautman@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...

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.

done it!

SELECT Hirer.Hirer_ID, Hirer.Hirer_First_Name, Hirer.Hirer_Surname,
Hirer.Hirer_Address, Hirer.Hirer_DOB, accounts.Account_number,
Account_Type.Account_Type, account_status.Account_Status,
Clients.Client_Name, Agents.Agents_Name, Vehicle.Vehicle_Registration,
hirer.Hirer_Accessed_by
From hirer left outer join Accounts on (hirer.hirer_ID =
Accounts.hirer_ID)
left outer join account_status on (Accounts.account_status_ID =
account_status.account_status_ID)
left outer join Account_Type on (Accounts.Account_Type_ID =
Account_Type.Account_Type_ID)
left outer join Vehicle on (Accounts.Account_ID = Vehicle.Account_ID)
left outer join Clients on (Accounts.Client_ID = Clients.Client_ID)
left outer join Agents on (Accounts.Agents_id = Agents.Agents_id)
join Arrangement on (Accounts.Account_ID = Arrangement.Account_ID)
left join Arrangement AS a1 ON
Arrangement.Arrangement_Entered_into_Date <
a1.Arrangement_Entered_into_Date
AND
Accounts.Account_ID = a1.Account_ID
WHERE a1.Account_ID IS NULL AND Account_Status.Account_Con_Live='1'
AND
Arrangement.Arrangement_Met='0' AND CURDATE() >
Arrangement.Arrangement_Expires_Date

On 23 Mar, 10:51, Anony <y...@no.com> wrote:
> done it!
>
> SELECT Hirer.Hirer_ID, Hirer.Hirer_First_Name, Hirer.Hirer_Surname,
> Hirer.Hirer_Address, Hirer.Hirer_DOB, accounts.Account_number,
> Account_Type.Account_Type, account_status.Account_Status,
> Clients.Client_Name, Agents.Agents_Name, Vehicle.Vehicle_Registration,
> hirer.Hirer_Accessed_by
> From hirer left outer join Accounts on (hirer.hirer_ID =
> Accounts.hirer_ID)
> left outer join account_status on (Accounts.account_status_ID =
> account_status.account_status_ID)
> left outer join Account_Type on (Accounts.Account_Type_ID =
> Account_Type.Account_Type_ID)
> left outer join Vehicle on (Accounts.Account_ID = Vehicle.Account_ID)
> left outer join Clients on (Accounts.Client_ID = Clients.Client_ID)
> left outer join Agents on (Accounts.Agents_id = Agents.Agents_id)
> join Arrangement on (Accounts.Account_ID = Arrangement.Account_ID)
> left join Arrangement AS a1 ON
> Arrangement.Arrangement_Entered_into_Date <
> a1.Arrangement_Entered_into_Date
> AND
> Accounts.Account_ID = a1.Account_ID
> WHERE a1.Account_ID IS NULL AND Account_Status.Account_Con_Live='1'
> AND
> Arrangement.Arrangement_Met='0' AND CURDATE() >
> Arrangement.Arrangement_Expires_Date

Well done!

On 23 Mar 2007 04:50:53 -0700, "Captain Paralytic"
<paul_lautman@yahoo.com> wrote:

>On 23 Mar, 10:51, Anony <y...@no.com> wrote:
>> done it!
>>
>> SELECT Hirer.Hirer_ID, Hirer.Hirer_First_Name, Hirer.Hirer_Surname,
>> Hirer.Hirer_Address, Hirer.Hirer_DOB, accounts.Account_number,
>> Account_Type.Account_Type, account_status.Account_Status,
>> Clients.Client_Name, Agents.Agents_Name, Vehicle.Vehicle_Registration,
>> hirer.Hirer_Accessed_by
>> From hirer left outer join Accounts on (hirer.hirer_ID =
>> Accounts.hirer_ID)
>> left outer join account_status on (Accounts.account_status_ID =
>> account_status.account_status_ID)
>> left outer join Account_Type on (Accounts.Account_Type_ID =
>> Account_Type.Account_Type_ID)
>> left outer join Vehicle on (Accounts.Account_ID = Vehicle.Account_ID)
>> left outer join Clients on (Accounts.Client_ID = Clients.Client_ID)
>> left outer join Agents on (Accounts.Agents_id = Agents.Agents_id)
>> join Arrangement on (Accounts.Account_ID = Arrangement.Account_ID)
>> left join Arrangement AS a1 ON
>> Arrangement.Arrangement_Entered_into_Date <
>> a1.Arrangement_Entered_into_Date
>> AND
>> Accounts.Account_ID = a1.Account_ID
>> WHERE a1.Account_ID IS NULL AND Account_Status.Account_Con_Live='1'
>> AND
>> Arrangement.Arrangement_Met='0' AND CURDATE() >
>> Arrangement.Arrangement_Expires_Date
>
>Well done!

Thanks, 4 days on that sucker!