I have three joined tables 'Student' , ‘StudentTransportOrder’ and 'Transport' in SQL server
I have made a ‘StudentActivity’ view for this
the 'Student' table each student record has a TransportOrderID
however the table I have joined to has a 'VehicleID' this is recorded into the ‘StudentTransportOrder’ table if transport is used, even though the vehicle type may be different e.g. Train or Car
Unfortunately the same transport id has been entered for the same date and time for a student records. The StudentActivity view already brings records based on where conditions, but I also need to remove the duplicate records, preferably keep the records where if a student has used transport on a date and time that only one distinct VehicleID is retued and preferably the where the transport type is TransportVehicle = Car
How can I amend the view , without deleting records from the main tables, also bring back records even if no transport has been ordered
Please help