title: Return stock in a warehouse even if there is no row for that given stock
body: <p>I have 5 different tables:</p>
  <ul>
  <li>Toasters: product name (foreign key to products and primary key), slots, serial</li>
  <li>Microwaves: product name (same as toaster), wattage</li>
  <li>Products: product name (primary key)</li>
  <li>Stock: product (fk to product), warehouse (fk to warehouse), amount</li>
  <li>Warehouse: name (primary key)</li>
  </ul>
  <p>toasters and microwaves are child tables of products (although its not using postgres inheritance, since there are issues with it). They represent different models of toasters (simplified to just slots and wattage here). Every toaster and microwave has exactly 1 entry in the products table.</p>
  <p>Now the goal is to create a query that essentially gives me an amount of all products across all warehouses for a given list of product names. The problem is, that some warehouses may not have a stock entry for a certain product. They also have either one stock per product or none.</p>
  <p>I have managed to make it work for a single warehouse:</p>
  <pre><code>--join together all 3 product tables and select all desired products
  WITH selIProducts AS(
      SELECT
          --Get the products category by checking if the table is part of the query
          (CASE
              WHEN toasters IS NOT NULL THEN 'toasters'
              WHEN microwaves IS NOT NULL THEN 'microwaves'
              ELSE 'ERROR'
          END) as category,
          products.name as productName,
          *
      FROM products
          --I need a full join to include everything
          FULL JOIN toasters ON toasters.name=products.name
          FULL JOIN microwaves ON microwaves.name=products.name
      WHERE 
          products.name IN (
                              'TOASTMASTER 3000',
                              'TOASTMASTER 3000Rev01',
                              'A3452 Ultra Microwave Oven',
                              )
  ),
  warehouseStock AS
  (
      --only works with one inventory
      SELECT * FROM STOCK
      WHERE stock.warehouse='WH-1'
  )
  -- left join to ensure all item categories are included
  SELECT COALESCE(warehouseStock.amount,0) as amount,* FROM selProducts
      LEFT JOIN warehouseStock ON selIProducts.itemId=warehouseStock.item
  </code></pre>
  <p>It tried replacing WHERE stock.warehouse='WH-1' with WHERE stock.warehouse IN ('WH-1','WH-2') but that doesn't work since the desired product types are only joined once, instead of once per warehouse.</p>
  <p>The final result should look like this:</p>
  <pre><code>Warehouse    productName                    amount    wattage   slots    category
  
  WH-1         TOASTMASTER 3000               0         null      2        toasters
  WH-1         TOASTMASTER 3000Rev01          1         null      3        toasters
  WH-1         A3452 Ultra Microwave Oven     1         3000      null     microwave
  WH-2         TOASTMASTER 3000               2         null      2        toasters
  WH-2         TOASTMASTER 3000Rev01          0         null      3        toasters
  WH-2         A3452 Ultra Microwave Oven     0         3000      null     microwave
  </code></pre>
  <p>I don't know how I am I should get postgres to return a null when there isn't a stock in a given warehouse.</p>
  <p>Does anybody have any ideas?</p>