November 15, 2013 at 2:46 pm
Using MSSQL 2008 R2
In the from clause below I am unable to understand what the result set will be.
I do not understand how nested joins work and this confuses me to no end.
I've been tasked to determine why a single invoice without a part number and having a zero balance
prints completely blank.
I removed the where clause thinking it was an obvious issue.
But removing the zero line amount restriction did not fix the problem.
So I'm moving up to the from clause. Umph!
The cust_order_binary table holds order notes.
Not all orders have notes. I'm guessing that's why there is a right outer join to customer_order
Every customer_order gets a billing and shipping address
Then the join of the notes table (cust_order_binary) to the customer_order_Line.
Then I get lost when joining to invoices (receivable and receivable_line).
"RIGHT OUTER JOIN RECEIVABLE
LEFT OUTER JOIN CUSTOMER"
I don't understand this.
Can anyone help?
[Code="sql"]
FROM
CUST_ORDER_BINARY RIGHT OUTER JOIN CUSTOMER_ORDER
INNER JOIN CUST_ORDER_LINE
ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID
LEFT OUTER JOIN CUST_ADDRESS
ON CUSTOMER_ORDER.CUSTOMER_ID = CUST_ADDRESS.CUSTOMER_ID
AND CUSTOMER_ORDER.SHIP_TO_ADDR_NO = CUST_ADDRESS.ADDR_NO
ON CUST_ORDER_BINARY.CUST_ORDER_ID = CUSTOMER_ORDER.ID
LEFT OUTER JOIN CUST_LINE_BINARY
ON CUST_ORDER_LINE.CUST_ORDER_ID = CUST_LINE_BINARY.CUST_ORDER_ID
AND CUST_ORDER_LINE.LINE_NO = CUST_LINE_BINARY.CUST_ORDER_LINE_NO
RIGHT OUTER JOIN RECEIVABLE
LEFT OUTER JOIN CUSTOMER
ON RECEIVABLE.CUSTOMER_ID = CUSTOMER.ID
LEFT OUTER JOIN RECEIVABLE_BINARY
ON RECEIVABLE.INVOICE_ID = RECEIVABLE_BINARY.INVOICE_ID
LEFT OUTER JOIN SALES_TAX RIGHT OUTER JOIN RECEIVABLE_LINE
ON SALES_TAX.ID = RECEIVABLE_LINE.REFERENCE
LEFT OUTER JOIN SHIPPER
ON RECEIVABLE_LINE.PACKLIST_ID = SHIPPER.PACKLIST_ID
ON RECEIVABLE.INVOICE_ID = RECEIVABLE_LINE.INVOICE_ID
LEFT OUTER JOIN RECV_LINE_BINARY
ON RECEIVABLE_LINE.INVOICE_ID = RECV_LINE_BINARY.INVOICE_ID
AND RECEIVABLE_LINE.LINE_NO = RECV_LINE_BINARY.RECV_LINE_NO
ON CUST_ORDER_LINE.CUST_ORDER_ID = RECEIVABLE_LINE.CUST_ORDER_ID
AND CUST_ORDER_LINE.LINE_NO = RECEIVABLE_LINE.CUST_ORDER_LINE_NO
WHERE
(RECEIVABLE.INVOICE_ID LIKE @oPARAMETER1)
AND (RECEIVABLE_LINE.AMOUNT <> 0)
ORDER BY RECEIVABLE.INVOICE_ID,RECEIVABLE_LINE.LINE_NO
[/CODE]
November 15, 2013 at 2:59 pm
That query is a mess and hard to read for sure, but is the question why is a certain record not getting returned? If so, I would start with the first join (binary and customer order) and verify that it gets returned in that query then add in the inner join and the right outer joins and then evaluate the where condition in steps (one table at a time). After that you should be able to see where the record is getting dropped off.
November 16, 2013 at 7:16 am
You're also missing some JOIN criteria here:
LEFT OUTER JOIN SALES_TAX RIGHT OUTER JOIN
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 16, 2013 at 9:05 am
Grant Fritchey (11/16/2013)
You're also missing some JOIN criteria here:
LEFT OUTER JOIN SALES_TAX RIGHT OUTER JOIN
No, the nest join is nicely matched up with each join having it's corresponding ON clause.
The ON clause for that RIGHT JOIN is ON SALES_TAX.ID = RECEIVABLE_LINE.REFERENCE, and the on clause for that LEFT JOIN is
ON RECEIVABLE.INVOICE_ID = RECEIVABLE_LINE.INVOICE_ID
Of course it's pretty well impossible to work out by inspection which ON clause belongs to which JOIN in a mess like this, since the layout looks as if it were intended to make it as hard as possible to understand. In my opinion, the whole thing is a crime against all decent coding standards, and putting two join clauses on the same line in the middle of a shambles like this compounds the offense, so I'm not at all surprised that you thought the clauses didn't match upproperly. I didn't work it out myself by looking at the code as it stands. If I had access to an SQL formatter which could make something acceptable out of something as complex as this I would have started by using that; unfortunately I don't. So I wrote some code (SQL of course: gross misuse of the language, but who cares) to label the JOIN operators and ON clauses with comments indicating their nesting levels, and then it's obvious what matches what because the ON clause which matches a join is the first one after the that join that has the same nesting level as it. I didn't get any zero or negative nesting levels, so I know that each join has an ON clause; but I didn't check whether the ON clauses refer to tables which are actually involved in the joins (including all tables involved in any nested join which is one of the two legs of this join) - I guess the OP can do that.
Any way, here is the labelled FROM clause. It may help the OP to understand this horribly laid out nested join.
CUST_ORDER_BINARY RIGHT OUTER /*1J*/ JOIN CUSTOMER_ORDER
INNER /*2J*/ JOIN CUST_ORDER_LINE
/*2O*/ ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID
LEFT OUTER /*2J*/ JOIN CUST_ADDRESS
/*2O*/ ON CUSTOMER_ORDER.CUSTOMER_ID = CUST_ADDRESS.CUSTOMER_ID
AND CUSTOMER_ORDER.SHIP_TO_ADDR_NO = CUST_ADDRESS.ADDR_NO
/*1O*/ ON CUST_ORDER_BINARY.CUST_ORDER_ID = CUSTOMER_ORDER.ID
LEFT OUTER /*1J*/ JOIN CUST_LINE_BINARY
/*1O*/ ON CUST_ORDER_LINE.CUST_ORDER_ID = CUST_LINE_BINARY.CUST_ORDER_ID
AND CUST_ORDER_LINE.LINE_NO = CUST_LINE_BINARY.CUST_ORDER_LINE_NO
RIGHT OUTER /*1J*/ JOIN RECEIVABLE
LEFT OUTER /*2J*/ JOIN CUSTOMER
/*2O*/ ON RECEIVABLE.CUSTOMER_ID = CUSTOMER.ID
LEFT OUTER /*2J*/ JOIN RECEIVABLE_BINARY
/*2O*/ ON RECEIVABLE.INVOICE_ID = RECEIVABLE_BINARY.INVOICE_ID
LEFT OUTER /*2J*/ JOIN SALES_TAX RIGHT OUTER /*3J*/ JOIN RECEIVABLE_LINE
/*3O*/ ON SALES_TAX.ID = RECEIVABLE_LINE.REFERENCE
LEFT OUTER /*3J*/ JOIN SHIPPER
/*3O*/ ON RECEIVABLE_LINE.PACKLIST_ID = SHIPPER.PACKLIST_ID
/*2O*/ ON RECEIVABLE.INVOICE_ID = RECEIVABLE_LINE.INVOICE_ID
LEFT OUTER /*2J*/ JOIN RECV_LINE_BINARY
/*2O*/ ON RECEIVABLE_LINE.INVOICE_ID = RECV_LINE_BINARY.INVOICE_ID
AND RECEIVABLE_LINE.LINE_NO = RECV_LINE_BINARY.RECV_LINE_NO
/*1O*/ ON CUST_ORDER_LINE.CUST_ORDER_ID = RECEIVABLE_LINE.CUST_ORDER_ID
AND CUST_ORDER_LINE.LINE_NO = RECEIVABLE_LINE.CUST_ORDER_LINE_NO
The labels are /*<nesting level><O or J>*/ with O for ON clause and J for JOIN operator, and nesting level starting at 1, not at 0.
Tom
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply