Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jwells
Starting Member
17 Posts |
 Posted - 2013-04-19 : 10:24:02
|
| 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= 0I can see the issue is probably with the Trips.Started = 0 but don't know to include Workorders that have no unStarted Trips. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-19 : 10:27:12
|
Try moving the where clause on Trips.Started to the join condition:Select Workorders.*, Min(Trips.Date) From Workorders left outer join Trips on Workorders.ID = Trips.WOID and Trips.Started= 0Where OwnerID = 1 |
 |
|
|
jwells
Starting Member
17 Posts |
Posted - 2013-04-19 : 10:56:47
|
| Great! You are awsome. Thank you for the help. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-19 : 12:18:31
|
| You are quite welcome - glad to help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
Need Help with a Select Statement