suppose that i have 2 tables:
TBCars(Keep information about one car like model,plaqueNo, owner,...)
TBServices(keep service information,like serviceType,serviceDate,..)
in this system, when a car for a first time enter to this system, i insert 1 record for it into TBCar, from second time, every time this car enter to system, one record inserted into TBServices(that refer to 1 record in TBCar,1-& relationship).
the expected result from this view, return n record that n is number of records in TBServices. but i want 1 record in result for every car in TBCar.
in this result grid, serviceDate & serviceType is the last record inserted into TBServices.
& i want records in TBCars that dont have any child in TBServices come into my VIEW.
SELECT A.IDCAR,A.MODEL,A.PLAQUENO,A.OWNER,B.SERVICEDATE,B .SERVICETYPE
FROM TBCARS A, TBSERVICES B
WHERE A.IDCAR = B.IDCAR AND
B.SERVICEDATE IN (SELECT MAX(SERVICEDATE) FROM TBSERVICES WHERE IDCAR = A.IDCAR)
SELECT A.IDCAR,A.MODEL,A.PLAQUENO,A.OWNER,NULL,NULL FROM
TBCARS A LEFT JOIN TBSERVICES B
A.IDCAR = B.IDCAR
B.IDCAR IS NULL