SQL Join without multiple LEFT

Question

I have the following tables in an SQL DB

Vehicles, RepairCharges, TowCharges,

There will always be only 1 record for Vehicle, but multiple records for the other tables. My current LEFT OUTER Join works however if there are multiple entries in the joined tables then its returning just as many rows.

My question is, I need to create a SQL view that will JOIN these tables, however only return a single record for the Vehicle Table, even if there are multiple records in the joined tables. Is this possible with a VIEW, or do i have to use a different approach ?

EDIT: From the answers, I realize as I originally thought, this would not be possible by design. Given the same scenario, how would you approach this ?

The end result is to have a table showing 1 line for each vehicle and in that same line show the first towing and repair charges, then if there were more towing or repair charges, show a new line for each of these, without duplicating the vehicle information.

Answer 1

NO, unless you use an AGGREGATE function, joins from 1 to MANY will do as design return 1 to MANY.

Do you have specific, such as only return the latets, or most costly.

Show your query, and specify which of the many you require.

Answer 2

Aside from aggregating, You could try adding a DISTINCT clause to your query.

Answer 3

The result of a JOIN will always contain the number of records in left multiplied by number of records in right. That's part of the JOIN definition, and doesn't change anything if you make it a view or keep it ad-hoc SQL.

What you need to do is change what's joined to so it has only one row. You need to express a criteria that chooses which of the possible rows in RepairCharges and TowCharges are you interested in, from the many possible candidates. Last date? Highest charge? Closest to full moon phase? Then you join with only that row.

Example:

SELECT * FROM Vechicles 
CROSS APPLY (
 SELECT TOP (1) * 
 FROM RepairCharges 
 WHERE VechicleID = Vechicles.VechicleID
 ORDER BY RepairDate DESC) AS LastRepair;