Problem joining three tables

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.

Without seeing the source data, I'm not sure how to help you. The code, on the surface, looks to be correct. Should there only be one entry in tbl_bl_order per entry in tbl_bol? Should there only be one entry in tbl_bol_carrier per entry in tbl_bol_order?

BTW, I'd suggest to stop using "tbl_" as a prefix on your table names.

  1. Can you use the specific table name in the description of your problem rather than "Table 1", "Table 2" etc. I have problem matching that to the query that you posted

  2. what is the relationship between those tables ? Looks like you are missing one join condition. Without the table schema and sample data it is hard to tell.

  3. Your query return 5 columns, but the result you posted is only 3.

Maybe this will help. Here is a screen shot of a bill of lading I'm trying to replicate in query form.

So in the "Customer Order Information" there are two weights. Those are the weights that are in tbl_bl_order. There are two more weights in the "Carrier Information", those are the weights that are in the tbl_bol_carrier table. The only reason I need the tbl_bol table in here is because that's the master table where all the orders go, all three of them have the BOL_ID I'm trying to join them together on.

And I hear about you about the TBL prefix, but that's also the name of the company so I think that's what they're putting it there for.

Have you tried using Design Query Editor in SMSS to link the tables?

I have, the query I posted is a result of using the editor.

I guess I don't know how to provide enough relevant info here, I'll try to figure it out on my own.

you have shown us what you want. But you have not shown us where you are now.

for others to help you, please provide your table structure / schema and some sample data from those tables