I have four tables: Documents, Items, Articles and DocumentRelations. There are three types of documents in documents, this question concerns only two of them: order and invoice. An invoice is created based on orders. An invoice may have several orders, and one order may have several invoices. An order and invoice can have several elements. The DocumentRelations table contains the relationship between the orders and the invoices from which the invoice was created, from which order / orders.
Documents: ID | Name -- | -------- 1 | O/2017/1 2 | I/2017/1 3 | O/2017/2 4 | I/2017/2 5 | O/2017/3 6 | O/2017/4 .. | ... 99 | X/2017/1
Ttems: ID | ArticleID | DocumentID | Quantity -- | --------- | ---------- | -------- 1 | 1 | 1 | 12 2 | 1 | 2 | 3 3 | 2 | 3 | 41 4 | 2 | 4 | 41 5 | 1 | 4 | 59 6 | 1 | 5 | 59 7 | 3 | 6 | 7
Articles: ID | Name -- | ---- 1 | A 2 | B 3 | c
DocumentRelations: OrderID | InvoiceID ------- | --------- 1 | 2 3 | 4 5 | 4 8 | 9 8 | 10 8 | 11
Scenario 1: The corresponding order / s for invoice / s does not exist. The corresponding invoice / s for order / s does not exist.
Scenario 2: The corresponding invoices / s for order / s do not contain the same quantity of each item as the invoice / s. The corresponding order / s for invoice / does not contain the same quantity of each item as order / s.
This is where my problem begins: I need to get a list of items with quantities in each document in an easily comparable form. Thus, the order identifier, invoice identifier, product name, quantity in the order, quantity in the invoice.
OrderID | InvoiceID | ItemNameInOrder | QuantityInOrder | QuantityInInvoice ------- | --------- | --------------- | --------------- | ----------------- 1 | 2 | A | 12 | 3 3 | 4 | B | 41 | 41 5 | 4 | A | 59 | 59 6 | NULL | C | 7 | NULL NULL | 7 | B | NULL | 11 8 | 9 | A | 10 | 9 8 | 10 | A | 10 | 9 8 | 11 | A | 10 | 19
If the order for a specific invoice does not exist, leave null in the column with the name and quantity. The DocumentRelations table contains three types of documents, so documents with a third should not appear.
My first plan was to make two choices returning the columns I need, each for a document type. You can then join them based on the identifiers in DocumentRelations. But all this multiplies. About 3 times more than the expected result.
SELECT O.ID, I.ID, O.ArticleName, O.Quantity, I.Quantity FROM DocumentRelations R LEFT JOIN (SELECT D.ID, D.NumberString, I.Quantity, A.Name FROM Documents D JOIN Items I ON D.ID = I.DocumentID JOIN Articles A ON I.ArticleID = A.ID WHERE D.Name LIKE 'O/%') O ON R.OrderID = O.ID LEFT JOIN (SELECT D.ID, , I.Quantity, A.Name FROM Documents D JOIN Items I ON D.ID = I.DocumentID JOIN Articles A ON I.ArticleID = A.ID WHERE D.Name LIKE 'I/%') I ON R.InvoiceID = I.ID
The second is similar to the first, but without using DocumentRelations at the end. Create two options: one with the order ID, item name, number of items, and ID of the corresponding invoice. The second is the same, but for invoices. Attach them to the full outer join based on the column with the ID of the corresponding document, but the result will be the same.
SELECT O.ID, I.ID, O.ArticleName, O.Quantity, I.Quantity FROM (SELECT D.ID, D.NumberString, I.Quantity, A.Name, R.InvoiceID FROM Documents D JOIN Items I ON D.ID = I.DocumentID JOIN Articles A ON I.ArticleID = A.ID LEFT JOIN DocumentRelations R ON D.ID = R.OrderID WHERE D.Name LIKE 'O/%') O FULL OUTER JOIN ( SELECT D.ID, D.NumberString, I.Quantity, A.Name FROM Documents D JOIN Items I ON D.ID = I.DocumentID JOIN Articles A ON I.ArticleID = A.ID WHERE D.Name LIKE 'I/%') I ON O.InvoiceID = I.ID