Professional Web Applications Themes

Returning last met record only! - MySQL

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

  1. #1

    Default Returning last met record only!

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

  2. #2

    Default Re: Returning last met record only!

    On 21 Mar, 14:21, Anony <com> wrote: 

    Search on this NG for "strawberry query". This question gets asked a
    lot and the answer is always the strawberry query.

    Captain Guest

  3. #3

    Default Re: Returning last met record only!

    On 21 Mar 2007 07:23:54 -0700, "Captain Paralytic"
    <com> wrote:
     
    >
    >Search on this NG for "strawberry query". This question gets asked a
    >lot and the answer is always the strawberry query.[/ref]

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

  4. #4

    Default Re: Returning last met record only!

    On 21 Mar, 14:43, Anony <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > 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 -[/ref]

    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.

    Captain Guest

  5. #5

    Default Re: Returning last met record only!

    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
    Anony Guest

  6. #6

    Default Re: Returning last met record only!

    On 23 Mar, 10:51, Anony <com> wrote: 

    Well done!

    Captain Guest

  7. Moderated Post

    Default Re: Returning last met record only!

    Removed by Administrator
    Anony Guest
    Moderated Post

Similar Threads

  1. returning an inserted SQL record
    By JoeyTMann in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 22nd, 04:38 PM
  2. Functions returning RECORD
    By Craig Bryden in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 14th, 01:37 PM
  3. PHP-MySQL: Returning ID when new record is made?
    By Sugapablo in forum PHP Development
    Replies: 8
    Last Post: December 5th, 05:54 PM
  4. FileIndex MSIDXS error returning record
    By Tim in forum ASP.NET General
    Replies: 0
    Last Post: July 18th, 02:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139