title: SQL - select values from two different tables
body: <p>I have got a problem with selecting values from two different tables. Tables below:</p>
  
  <p>Material:</p>
  
  <pre><code>MaterialID | MaterialName
  -------------------------
  1111111    | Material1
  2222222    | Material2
  3333333    | Material3
  </code></pre>
  
  <p>Stock:</p>
  
  <pre><code>MaterialID | Location | Quantity
  ---------------------------------
  1111111    | LocA     | 10
  1111111    | LocB     | 20
  2222222    | LocC     | 15
  2222222    | LocD     | 10
  </code></pre>
  
  <p>My SQL query below:</p>
  
  <pre><code>SELECT
      [Material].[MaterialName] as 'Material Name',
      custom.quantity as 'Total Quantity',
  FROM
      [Material]
      inner join (
          SELECT
              [MaterialId] as 'Materialcode',
              SUM([Quantity]) as 'Quantity'
          from
              [Stock]
          group by
          [MaterialId]
      ) custom 
      on 
          custom.Materialcode = [Material].[MaterialId]
  </code></pre>
  
  <p>The result is:</p>
  
  <pre><code>Material Name | Total Quantity
  ------------------------------
  Material1     | 30
  Material2     | 25 
  </code></pre>
  
  <p>The problem is that in the result there is no information about Material3 (I know that the quantity is equal to 0 as it`s not in Stock table, but I need a result that will show all of the materials - like below:</p>
  
  <pre><code>Material Name | Total Quantity
  ------------------------------
  Material1     | 30
  Material2     | 25 
  Material3     | 0 
  </code></pre>
  
  <p>Is it possible?</p>