Add Join to Return All Dates

I have the query in the below snippet that works fine.  However, instead of returning only dates that contain values - as it does now, I, instead, need it to return ALL dates, and if there is no data for a given date, then it can just return every other column as null.

So, in an effort to accomplish this, I built a table named 'TSIDateLookup' with a column named 'DateFull' that contains ALL dates.

I was thinking that I just needed to LEFT JOIN this new table to the below query, but apparently I do not know how to accomplish that.  The join that needs to be made is between my new table/column dbo.TSIDateLookup.DateFull , and that would be equal to the following field in the existing query: dbo.p21_view_oe_hdr.order_date .  So, dbo.TSIDateLookup.DateFull  = dbo.p21_view_oe_hdr.order_date is what needs to somehow be added.

In case it matters, the new table is just that - a table.  Everything else being selected in the existing query is being pulled from a view.

Thanks for your help.

SELECT DISTINCT 
                      TOP 100 PERCENT dbo.p21_view_oe_hdr.company_id, dbo.p21_view_location.location_id AS [Sales Location ID], dbo.p21_view_oe_hdr.customer_id, 
                      dbo.p21_view_customer.customer_name, dbo.p21_view_customer.class_1id, dbo.p21_view_oe_hdr.order_no, dbo.p21_view_oe_hdr.order_date, 
                      dbo.p21_view_ship_to_salesrep.ship_to_id, dbo.p21_view_oe_hdr.ship2_name, dbo.p21_view_ship_to_salesrep.salesrep_id AS [Salesrep ID Ship2], 
                      p21_view_contacts_2.last_name + ', ' + p21_view_contacts_2.first_name AS [Salesrep Name Ship2], 
                      p21_view_contacts_2.first_name AS [Salesrep First Ship2], p21_view_contacts_2.last_name AS [Salesrep Last Ship2], 
                      dbo.p21_view_customer.salesrep_id AS [Salesrep ID Cust], 
                      p21_view_contacts_1.last_name + ', ' + p21_view_contacts_1.first_name AS [Salesrep Name Cust], 
                      p21_view_contacts_1.first_name AS [Salesrep First Cust], p21_view_contacts_1.last_name AS [Salesrep Last Cust], 
                      dbo.p21_view_oe_hdr_salesrep.salesrep_id AS [Salesrep ID Order], 
                      dbo.p21_view_contacts.last_name + ', ' + dbo.p21_view_contacts.first_name AS [Salesrep Name Order], 
                      dbo.p21_view_contacts.first_name AS [Salesrep First Order], dbo.p21_view_contacts.last_name AS [Salesrep Last Order], 
                      dbo.p21_view_oe_line.line_no, dbo.p21_view_oe_line.delete_flag AS OE_Lin_Del_Flag, dbo.p21_view_oe_line.cancel_flag AS OE_Lin_Cancel_Flag, 
                      dbo.p21_view_inventory_supplier.supplier_id AS [Primary Supplier ID], dbo.p21_view_supplier.supplier_name AS [Primary Supplier Name], 
                      dbo.p21_view_oe_line.item_id, dbo.p21_view_oe_line.qty_ordered, dbo.p21_view_oe_line.qty_per_assembly, dbo.p21_view_oe_line.unit_price, 
                      dbo.p21_view_oe_line.extended_price, dbo.p21_view_oe_line.assembly, dbo.p21_view_oe_line.unit_of_measure, 
                      dbo.p21_view_oe_line.commission_cost, dbo.p21_view_oe_line.commission_cost * dbo.p21_view_oe_line.qty_ordered AS Ext_Comm_Cost, 
                      dbo.p21_view_oe_line.extended_price - dbo.p21_view_oe_line.commission_cost * dbo.p21_view_oe_line.qty_ordered AS Profit, 
                      dbo.p21_view_oe_line.parent_oe_line_uid, dbo.p21_view_oe_line.disposition, dbo.p21_view_oe_hdr_salesrep.delete_flag AS Salesrep_Del_Flag, 
                      dbo.p21_view_oe_hdr.rma_flag
FROM         dbo.p21_view_ship_to_salesrep INNER JOIN
                      dbo.p21_view_oe_line INNER JOIN
                      dbo.p21_view_oe_hdr ON dbo.p21_view_oe_line.order_no = dbo.p21_view_oe_hdr.order_no INNER JOIN
                      dbo.p21_view_location ON dbo.p21_view_oe_hdr.location_id = dbo.p21_view_location.location_id AND 
                      dbo.p21_view_oe_hdr.company_id = dbo.p21_view_location.company_id INNER JOIN
                      dbo.p21_view_address ON dbo.p21_view_location.location_id = dbo.p21_view_address.id INNER JOIN
                      dbo.p21_view_oe_hdr_salesrep ON dbo.p21_view_oe_hdr.order_no = dbo.p21_view_oe_hdr_salesrep.order_number INNER JOIN
                      dbo.p21_view_contacts ON dbo.p21_view_oe_hdr_salesrep.salesrep_id = dbo.p21_view_contacts.id INNER JOIN
                      dbo.p21_view_customer ON dbo.p21_view_oe_hdr.customer_id = dbo.p21_view_customer.customer_id AND 
                      dbo.p21_view_oe_hdr.company_id = dbo.p21_view_customer.company_id INNER JOIN
                      dbo.p21_view_contacts p21_view_contacts_1 ON dbo.p21_view_customer.salesrep_id = p21_view_contacts_1.id INNER JOIN
                      dbo.p21_view_inv_mast ON dbo.p21_view_oe_line.inv_mast_uid = dbo.p21_view_inv_mast.inv_mast_uid INNER JOIN
                      dbo.p21_view_inventory_supplier ON dbo.p21_view_inv_mast.inv_mast_uid = dbo.p21_view_inventory_supplier.inv_mast_uid INNER JOIN
                      dbo.p21_view_inventory_supplier_x_loc ON 
                      dbo.p21_view_inventory_supplier.inventory_supplier_uid = dbo.p21_view_inventory_supplier_x_loc.inventory_supplier_uid AND 
                      dbo.p21_view_oe_line.source_loc_id = dbo.p21_view_inventory_supplier_x_loc.location_id INNER JOIN
                      dbo.p21_view_supplier ON dbo.p21_view_inventory_supplier.supplier_id = dbo.p21_view_supplier.supplier_id ON 
                      dbo.p21_view_ship_to_salesrep.company_id = dbo.p21_view_oe_hdr.company_id AND 
                      dbo.p21_view_ship_to_salesrep.ship_to_id = dbo.p21_view_oe_hdr.address_id INNER JOIN
                      dbo.p21_view_contacts p21_view_contacts_2 ON dbo.p21_view_ship_to_salesrep.salesrep_id = p21_view_contacts_2.id
WHERE     (dbo.p21_view_oe_line.delete_flag = 'N') AND (dbo.p21_view_oe_line.item_id <> 'MEMO') AND (dbo.p21_view_oe_line.cancel_flag = 'N') AND 
                      (dbo.p21_view_oe_line.parent_oe_line_uid = 0) AND (dbo.p21_view_oe_line.disposition <> 'C' OR
                      dbo.p21_view_oe_line.disposition IS NULL) AND (dbo.p21_view_oe_hdr_salesrep.delete_flag = 'N') AND (dbo.p21_view_oe_hdr.company_id = '01') 
                      AND (dbo.p21_view_oe_hdr.delete_flag = 'N') AND (dbo.p21_view_oe_hdr.projected_order = 'N') AND (dbo.p21_view_oe_hdr.cancel_flag = 'N') AND 
                      (dbo.p21_view_inventory_supplier_x_loc.primary_supplier = 'Y')
ORDER BY dbo.p21_view_oe_hdr.order_date, dbo.p21_view_oe_line.line_no

Select allOpen in new window