SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Help with a Select Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jwells
Starting Member

17 Posts

Posted - 04/19/2013 :  10:24:02  Show Profile  Reply with Quote

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.

James K
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 04/19/2013 :  10:27:12  Show Profile  Reply with Quote
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= 0
Where OwnerID = 1
Go to Top of Page

jwells
Starting Member

17 Posts

Posted - 04/19/2013 :  10:56:47  Show Profile  Reply with Quote
Great! You are awsome. Thank you for the help.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 04/19/2013 :  12:18:31  Show Profile  Reply with Quote
You are quite welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/20/2013 :  02:42:35  Show Profile  Reply with Quote
quote:
Originally posted by jwells

Great! You are awsome. Thank you for the help.



See the reason here

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000