Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: View Design Problem

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

    now i build a VIEW with these fields:

    IDCar,Model,PlaqueNo,Owner,ServiceType,ServiceDate

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    HI

    try 'something' better than this

    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)
    UNION
    SELECT A.IDCAR,A.MODEL,A.PLAQUENO,A.OWNER,NULL,NULL FROM
    TBCARS A LEFT JOIN TBSERVICES B
    ON
    A.IDCAR = B.IDCAR
    WHERE
    B.IDCAR IS NULL

    ;-)
    Cheers....

    baburajv

Posting Permissions

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