I'm trying to do a query using two tables. Workorders and Trips. There may be many Trips associated with each Workorder or may be non at all. I want to query all the Workorders that are mine plus an additional date field from the Trips table which I will call NewETA. So I want to list each Workorder along with it's asociated NewETA. NewETA will be the earliest Trip Date of a Trip that has not been started yet. I have a bit field in Trips as Started to indicate whether the Trip has been started or not.
So, I want to select all workorders assigned to me AND show the earliest unStarted TripDate. If a Trip has not been created or there are trips but they have all been started then the TripDate needs to be returned as Null.
My problem with my select statement is it only returns Workorders that have unstarted trips. It does not return Workorders that either have no trips at all or it has trips but they have all been Started. Here is what I've been working with:
Select Workorders.*, Min(Trips.Date) From Workorders left outer join Trips on Workorders.ID = Trips.WOID Where OwnerID = 1 and Trips.Started= 0
I can see the issue is probably with the Trips.Started = 0 but don't know to include Workorders that have no unStarted Trips.