[SQL Server] Mixed joins

---

Mixed joins Posted: 26 Mar 2013 01:33 AM PDT I have a table (tblTimesheetWOErrors) containing an error list with (ErrorID, WONbr, TimesheetID, Username, Date). The WONbr refers to a unique (Non-PK) index on the tblWorkOrder table, and may be invalid. I have a Timesheet Master that identifies the Vendor as well. Problem is, I can't seem to get the mixed joins to work properly. Either I get no rows returned, or only valid WOs returned...that's what is happening here. I read up on mixed joins, and followed the advice I found to eliminate them by using "sub-selects". Herewith my SQL. Can anyone help me here?[code="sql"]SELECT CASE WHEN v.[Vendor Name] IS NULL THEN 'No Vendor' ELSE v.[Vendor Name] END AS TD, e.TimesheetID AS TD, e.UserName AS TD, e.WorkOrderNbr AS TD, format(e.ErrorDate, 'd') AS TD, CASE WHEN w.WOExpiry IS NULL THEN 'Invalid WO' ELSE format(w.WOExpiry, 'd') END AS TDFROM tblTimesheetWOErrors e join (select e1.TimesheetID, wo.* from tblTimesheetWOErrors e1 left join tblWorkOrders wo on e1.WorkOrderNbr=wo.WONbr ) w on e.TimesheetID=w.TimesheetID join (select m1.tsid, v1.* from tblTimesheetMaster m1 join tblVendor v1 on m1.TSVendorID=v1.ID) v on e.TimesheetID=v.TSID[/code]tblTimesheetWOErrors:ID UserName ErrorDate WorkOrderNbr TimesheetID23 jamesshaffer 2013-03-23 00:00:00.000 TIALLOC 502524 nfox 2013-03-24 00:00:00.000 [b]655487[/b] 501325 jamesshaffer 2013-03-23 00:00:00.000 TIALLOC 502526 nfox 2013-03-24 00:00:00.000 [b]655487[/b] 501327 jamesshaffer 2013-03-23 00:00:00.000 TIALLOC 502528 nfox 2013-03-24 00:00:00.000 [b]655487[/b] 5013(Bold WONbrs are invalid)Results:wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WOwConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WOwConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WOwConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WOwConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WOwConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WOwConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WOwConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WOwConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610