To Join or Not to Join

Transact-SQL
1

I've created a query that I believed was working correctly, but found that a few records were missing. What I found is that a few records do not have any Miscellaneous charges, so there's no record at all in dbo.Miscellaneous_Charge. This breaks my query as this is one of my joins.

indent preformatted text by 4 spaces   INNER JOIN dbo.Miscellaneous_Charge mc ON oh.Order_Header_ID = mc.Order_Header_ID

How can I make the JOIN available if oh.Order_Header_ID = mc.Order_Header_ID AND remove the JOIN if it doesn't without blowing up the SELECT and WHERE statement when it doesn't? This one has me stumped.

Here's my full query:

indent preformatted text by 4 spaces

SELECT DISTINCT

CAST(COALESCE(LEFT(od.Part_Family_Code,2),0) AS decimal (10,2)) AS FBGM,
ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) AS Budgeted,
ROUND((oh.Gross_Amount-(oh.Total_Labor_Cost+oh.Total_Material_Cost+mc.Unit_Cost))/ISNULL(NULLIF(oh.Gross_Amount,0),1),4)*100 AS Actual,
rsn.Status AS Manager_Review_Status,
oh.Gross_Amount AS Gross_Amount,
oh.Total_Labor_Cost AS Total_Labor_Cost,
oh.Total_Material_Cost AS Total_Material_Cost,
mc.Unit_Cost AS Misc_Unit_Cost,
mc.Miscellaneous_Charge_Code,
CONVERT(char(10),od.Projected_Ship_Date,101) AS Projected_Ship_Date,
od.User_Text2 AS Reporting_Cycle_Code,
CONVERT(char(10),tt.Time_End,101) AS End_Ticket_Date,
oh.Order_Number AS Order_Number,
od.Work_Code AS Work_Code,
sco.Salesman_Name AS Salesman_Name,
cd.Commission_Percent AS Commission_Percent,
od.Product_Code AS Product_Code,
od.Part_Number AS Part_Number,
cd.Commissionable AS Commissionable,
tt.Pieces_Good AS Pieces_Good

FROM dbo.Order_Detail od
LEFT OUTER JOIN dbo.Routing_Header rh ON od.Order_Header_ID = rh.Order_Header_ID
LEFT OUTER JOIN dbo.vRouting_Step_Number_Line_Item_Specification rsn ON rh.Routing_Header_ID = rsn.Routing_Header_ID
LEFT OUTER JOIN dbo.Time_Ticket tt ON rsn.Routing_Header_ID = tt.Routing_Header_ID
LEFT OUTER JOIN dbo.Order_Header oh ON od.Order_Header_ID = oh.Order_Header_ID
LEFT OUTER JOIN dbo.Quantity_Break_Detail qbd ON od.Order_Detail_ID = qbd.Order_Detail_ID
LEFT OUTER JOIN dbo.Billing_Detail bd ON oh.Part_Number_ID = bd.Part_Number_ID
LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders sco ON od.Order_Detail_ID = sco.Order_Detail_ID
LEFT OUTER JOIN dbo.Commission_Distribution cd ON od.Order_Detail_ID = cd.Order_Detail_ID
LEFT OUTER JOIN dbo.vSalesman_Code sc ON cd.Salesman_Code = sc.Salesman_Code
LEFT OUTER JOIN dbo.Employee_Code ec ON od.Employee_Code_Routed_By = ec.Employee_Code
LEFT OUTER JOIN dbo.User_Code uc ON ec.User_Code = uc.User_Code
INNER JOIN dbo.Miscellaneous_Charge mc ON oh.Order_Header_ID = mc.Order_Header_ID

WHERE
qbd.Profit_Percent <> 0 AND
od.Profit_Percent <> 0 AND
od.Part_Family_Code NOT IN ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND
od.Company_Code ='AIN' AND
mc.Unit_Cost <> 0 AND
ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) < 95 AND
mc.Miscellaneous_Charge_Code <> 'FREIGHT' AND
rsn.Operation_Code = 'FINAL MANAGER REVIEW' AND
rsn.Status IN ( 'Closed' , 'Finished', 'Pending', 'Future', 'Current' ) AND
od.User_Text2 LIKE ('%Sebatien%')

ORDER BY od.Work_Code, oh.Order_Number, sco.Salesman_Name ASC

2

Typically you'd change the INNER JOIN to a LEFT OUTER JOIN, like so:

LEFT OUTER JOIN dbo.Miscellaneous_Charge mc ON oh.Order_Header_ID = mc.Order_Header_ID

3

Scott,

A LEFT OUTER JOIN would give me all records in the previous joins and only the records in dbo.Miscellaneous_Charge that have oh.Order_Header_ID = mc.Order_Header_ID

Some records don't have any entry in mc.Order_Header_ID, so these records don't show. I'm thinking it's a CASE statement, but haven't been able to come up with a complete plan yet.

Thanks for looking.

4

Not following your reasoning at all, but good luck with this.

5

Changing to an outer join on for the table Miscellaneous_Charge won't change the results - because you are filtering on columns from that table in the where clause.

If you need results to be returned for those items that don't have any rows in that table - you have to use an outer join and you will also need to figure out how to validate those that do exist, but do not have the required values.

You cannot move that criteria to the join - as then you could not determine whether or not the NULL row was returned because nothing exists or because they do exist but don't have valid values.

Or - you move the criteria to the join - use an outer join - and if that order doesn't have any matching rows from miscellaneous charges then you don't calculate any value for the Unit_Cost.

6

Oops, OK, yeah, I didn't see the mc. references in the WHERE. Maybe this:

LEFT OUTER JOIN dbo.Miscellaneous_Charge mc ON oh.Order_Header_ID = mc.Order_Header_ID AND mc.Miscellaneous_Charge_Code <> 'FREIGHT' AND mc.Unit_Cost <> 0

WHERE
qbd.Profit_Percent <> 0 AND
od.Profit_Percent <> 0 AND
od.Part_Family_Code NOT IN ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND
od.Company_Code ='AIN' AND
ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) < 95 AND
rsn.Operation_Code = 'FINAL MANAGER REVIEW' AND
rsn.Status IN ( 'Closed' , 'Finished', 'Pending', 'Future', 'Current' ) AND
od.User_Text2 LIKE ('%Sebatien%')