I have three tables here. All three have a unique field called bol_id that I can join on. Table 1 just contains client names and addresses I need, Table 2 has weights associated with the customers, and Table 3 has weights associated with a carrier. All I want is the client name and the two weights together. I keep getting wrong information this this query:
SELECT tbl_bl_order.weight AS cust_weight, tbl_bol_carrier.weight AS carrier_weight, tbl_bol.bol_id,tbl_bol.bol_number, tbl_bol.client_name FROM tbl_bol INNER JOIN tbl_bl_order ON tbl_bol.bol_id = tbl_bl_order.bol_id INNER JOIN tbl_bol_carrier ON tbl_bl_order.bol_id = tbl_bol_carrier.bol_id WHERE (tbl_bol.ship_date BETWEEN CONVERT(DATETIME, '2015-09-20 00:00:00', 102) AND CONVERT(DATETIME, '2015-11-19 00:00:00', 102)) AND (tbl_bol.client_name LIKE 'seal%') AND (tbl_bol.bol_number = '04000044003440973')
Here is what it's displaying:
cust.wgt carr. wgt. Company 2204 6992 Company 2204 2214 Company 6352 6992 Company 6352 2214 Company
What it should be displaying is:
cust.wgt carr. wgt. Company 2204 6992 Company 6352 2214 Company
I'm sure this has something to do with the join but I'm not sure how to fix it.