title: SQL multiplying rows. Data from the same table next to each other in select
body: <p>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. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>    </p>
  
  <pre><code>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
  </code></pre>
  
  <p>    </p>
  
  <pre><code>Articles: 
  ID | Name
  -- | ----
  1  | A
  2  | B
  3  | c
  </code></pre>
  
  <p>    </p>
  
  <pre><code>DocumentRelations:
  OrderID | InvoiceID
  ------- | ---------
  1       | 2
  3       | 4
  5       | 4
  8       | 9
  8       | 10
  8       | 11
  </code></pre>
  
  <p>Scenario 1: Corresponding order/s to invoice/s doesn’t exist. Corresponding invoice/s to order/s doesn’t exist.</p>
  
  <p>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.</p>
  
  <p><strong>Here my problem begins:</strong> 
  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. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <pre><code>   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
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>