...

View Full Version : Getting last record for a field



rdl
05-18-2011, 01:35 PM
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

abduraooft
05-18-2011, 01:46 PM
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"

rdl
05-18-2011, 01:55 PM
Thanks for your prompt reply, but query returned 0 results :(

gvre
05-18-2011, 02:53 PM
Try

SELECT ReportVehicleID,RegistrationNo from table_name where
Relation='Stolen' AND
RegistrationNo NOT IN
(SELECT RegistrationNo from table_name where Relation='Recovered')

abduraooft
05-18-2011, 03:06 PM
Try

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

rdl
05-18-2011, 03:14 PM
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')

Horologe
05-20-2011, 04:38 AM
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

bazz
05-20-2011, 01:37 PM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum