Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    rdl
    rdl is offline
    New to the CF scene
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation Getting last record for a field

    Hi All,
    I have the following table which includes reportvehicleID (autonumber), report reference, vehicle registration number, relation (stolen, recovered) and date of relation.


    ReportVehicleID ReportRef RegistrationNo Relation RelationDate
    1 4 AAA342 Stolen 2011-02-02
    2 5 BBS222 Stolen 2011-02-20
    3 4 AAA342 Recovered 2011-05-17


    Now I want a list of stolen vehicles i.e. if AAA342 has been recovered I don't want it in the report.
    Thus the report would only show the BBS222.

    Can someone help me through this pls cause I have been trying a lot.

    THANKYOU FOR YOUR HELP.

    Regards,
    rdl

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,854
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Code:
    SELECT ReportVehicleID,RegistrationNo from  table_name where 
    Relation='Stolen' AND
    RegistrationNo NOT IN 
       (SELECT RegistrationNo from table_name where Relation!='Recovered')
    PS: I'd use two separate tables to store the two "Relations"
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    rdl
    rdl is offline
    New to the CF scene
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your prompt reply, but query returned 0 results

  • #4
    Regular Coder
    Join Date
    May 2011
    Posts
    240
    Thanks
    1
    Thanked 56 Times in 55 Posts
    Try
    Code:
    SELECT ReportVehicleID,RegistrationNo from  table_name where 
    Relation='Stolen' AND
    RegistrationNo NOT IN 
       (SELECT RegistrationNo from table_name where Relation='Recovered')

  • #5
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,854
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Quote Originally Posted by gvre View Post
    Try
    Code:
    SELECT ReportVehicleID,RegistrationNo from  table_name where 
    Relation='Stolen' AND
    RegistrationNo NOT IN 
       (SELECT RegistrationNo from table_name where Relation='Recovered')
    Yes, that should work.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #6
    rdl
    rdl is offline
    New to the CF scene
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    Table name is reportvehicle....the below worked

    SELECT *
    FROM reportvehicle A
    WHERE A.Relation = 'Stolen'
    AND NOT EXISTS (SELECT * from reportvehicle B
    WHERE B.RegistrationNo = A.RegistrationNo AND
    B.Relation = 'Recovered')

  • #7
    New Coder
    Join Date
    May 2011
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It would be easier to restructure the table into
    ReportVehicleID ReportRef RegistrationNo StolenDate RecoveredDate
    1 4 AAA342 2011-02-02 2011-05-17
    2 5 BBS222 2011-02-20 0-0-0

  • #8
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by rdl View Post
    Table name is reportvehicle....the below worked

    SELECT *
    FROM reportvehicle A
    WHERE A.Relation = 'Stolen'
    AND NOT EXISTS (SELECT * from reportvehicle B
    WHERE B.RegistrationNo = A.RegistrationNo AND
    B.Relation = 'Recovered')
    yeh but why are you returning all fileds when you don't need to? And also, if you ever add a column to the tbale, that query may break. MUCH better only to query for the columns you need, by name.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Tags for this Thread

    Posting Permissions

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