Hi!
SELECT P.Person, P.Vehicle ;
FROM Purchase P ;
INNER JOIN Vehicle V ON P.Vehicle = V.Vehicle ;
WHERE V.Price IN ( ;
SELECT max(VV.Price) ;
FROM Vehicle VV ;
INNER JOIN Purchase PP ON PP.Vehicle = VV.Vehicle ;
WHERE PP.Person = P.Person )
NOTE:
Do not use max() and GROUP BY here instead, becuase it would be not reliable return of Vehicle name in such case. In other words, query like below returns incorrect results, though "looks" logical at the first look.
SELECT P.Person, P.Vehicle, max(V.Price) ;
FROM Purchase P ;
INNER JOIN Vehicle V ON P.Vehicle = V.Vehicle ;
GROUP BY P.Person
P.Vehicle in above query might not match to the price selected by max().
HTH.
>I have the following two tables (Purchase and vehicle) in a SQL Server table. Is there a way to produce the result set where it will show the expensive vehilce purchased by a person, using a single SQL Select (it can be nested.) I can bring down both tables in to Fox using SPT and then programmatically find them. But the problem is if the Purchas table is so big, then it will be an issue.
>
>Important Note: This is not my school home work! I just created an example for a real problem I am having now.
>
>Purchase
>Person, Vechicle, PuchaseID
>A, Car, 1
>A, Truck, 2
>A, Bike, 3
>B, Bike, 4
>B, Jet, 5
>C, Truck, 6
>D, 4WD, 7
>D, Bike, 8
>
>Vehicle
>
>Vehicle, Price
>Car, 5000
>Bike, 100
>Truck, 7000
>Jet, 30000
>4WD,6000
>
>
>Expected Result Set
>
>Expensive Vehicle
>Person ExpensiveVehicle
>A Truck
>B Jet
>C Truck
>D 4WD
Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.comICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs
It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.