Several inner joins producing too many rows

I'm working on a field trip request software for school districts.

Each field trip has an account attached to it that will be billed. It will also have one or more driver/vehicle combinations and mileage and driver rates associated with each driver/vehicle combination.

I'm working on an accounting report that will show, by account, a count of th开发者_运维知识库e trips that are assigned to that account, the total number of miles driven on that account times a certain charge basis, and a total number of hours each driver has driven on that trip multiplied by their payrate.

I have a field trip (tripid=1) with two vehicles and two drivers and I expect two rows of output. However, I am getting 4 rows; Two rows for vehicle 81 and two rows for vehicle 56. Is there something in my joins that's causing too many rows to be outputted?

What's weird is Mister Driver is on both vehicles in the output of my query and so is Generic Person.

select distinct
       tdv.tripid as tripid,
       ta.name as account,
       cb.chargebasisname as trip_type,
       cb.defaultdistancerate as distance_rate,
       (tc.odometerreturn-tc.odometerstart) as total_miles,
       datediff(hour, tc.actualoriginstarttime,tc.actualoriginreturntime) as total_hours,
       v.vehicle,
       pr.hourlyrate as driver_rate,
       e.firstname+' '+e.lastname as driver

from trip_tripdrivervehicle tdv

join trip_tripinformation ti
on ti.recordid = tdv.tripid

join trip_transportationaccounts ta
on ta.recordid = ti.accountid

join trip_invoicechargebasis cb
on cb.recordid = ta.defaultchargebasisid

join trip_tripcompletion tc
on tc.tripid = ti.recordid

join vehicles v
on v.recordid = tc.vehicleid

join trip_employeejobcategorypayrate pr
on pr.employeeid = tdv.driverid

join employees e
on e.recordid = tdv.driverid

where ti.triprequeststatusid = 7 and ti.recordid = 1

Here is my output:

https://lh6.googleusercontent.com/_Bbr20KcwLyw/TX5cwDhr7BI/AAAAAAAAbYE/qCfQtk6Xmeg/s800/sql_results.jpg

Looks like you also have two matches for the JOIN with table trip_tripcompletion - one that gives rows with total miles of 10 and another that gives rows with total miles of 50.

So each driver shows up with each total_miles option.

The JOIN condition for this table may need to be changed, or you can use GROUP BY along with MAX/MIN to show only the shortest/longest trip (depending on the need).