SQL multiplying rows. Data from the same table next to each other in select

Question

I have four tables: Documents, Items, Articles and DocumentRelations. In Documents there are three types of documents, this question relates only two of them: order and invoice. Invoice is generated based on orders. Invoice can have multiple orders and one order can have multiple invoices. Order and invoice can have multiple items. Table DocumentRelations contains relations between orders and invoices, which invoice was generated 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: Corresponding order/s to invoice/s doesn’t exist. Corresponding invoice/s to order/s doesn’t exist.

Scenario 2: Corresponding invoice/s to order/s doesn’t contain the same quantity of each item as invoice/s. Corresponding order/s to invoice/s doesn’t contain the same quantity of each item as order/s.

Here my problem begins: I need to get list of items with quantities in each document in easily comparable form. So, id of order, id of invoice, name of item, quantity in order, quantity in 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 order for specific invoice doesn’t exist, left null in column with name and quantity. Table DocumentRelations contain three types of document, so documents with third one shouldn’t appear.

My first plan was to make two selects returning columns that I need only, each for type of document. Then left join them based on IDs in DocumentRelations. But that multiples everything. About 3 times more than estimated 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

Second is similar to first one but without using DocumentRelations at the end. Create two selects, one with order id, item name, item quantity and id of corresponding invoice. Second is the same but for invoices. Join them with full outer join based on column with ID of corresponding document, but result is 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

Answer 1

I like the second one - you need just to add ArticleID to your conditions and you will get almost you want, except of missing Names for some strings. And we presume that you don't have TWO or MORE lines for the same article in one document:

SELECT O.ID, I.ID, O.ArticleName, O.Quantity, I.Quantity
  FROM (SELECT D.ID, D.NumberString, I.Quantity, A.Name, R.InvoiceID, D.ArticleID 
          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, D.ArticleID
          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 D.InvoiceID = I.ID
           AND D.ArticleID  = I.ArticleID