Problem joining three tables

Transact-SQL
1

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.

2

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.

3

  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.

4

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

zXsz.PNG768×422 20.2 KB

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.

5

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

6

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.

7

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