title: SQL - Calculation with Union All and Join
body: <p>I have 3 SQL tables, one for each vendor.  All the tables have 3 identical columns: (id), (spend), and (date).  (id) stands for product id.  (spend) is how much they spent on the product.  And (date) of the transaction.  </p>
  
  <p>Thanks to stackoverflow, I was able to sort by the total spent on each product (id) including all tables.  Here is the UNION ALL statement that works:</p>
  
  <pre><code>SELECT id, SUM(spend) as total
                      FROM (
                          SELECT id, spend, date 
                          FROM vendor1 
                          WHERE date BETWEEN '$dateStart' AND '$dateEnd'
                          UNION ALL
                          SELECT id, spend, date 
                          FROM vendor2
                          WHERE date BETWEEN '$dateStart' AND '$dateEnd'
                          UNION ALL
                          SELECT id, spend, date 
                          FROM vendor3
                          WHERE date BETWEEN '$dateStart' AND '$dateEnd'
                      ) as SpendTotal  
                      GROUP BY id 
                      ORDER BY total DESC
  </code></pre>
  
  <p>Now I would like to incorporate a new table (revTbl) in the SQL formula that lists revenue earned from each product (id).  This 4th table has the same column labeled (id) as the 3 mentioned above.  It also has the same (date) column.  However, this table has a different column named (rev).  </p>
  
  <p>I would like to write a SQL statement that sorts by the SUM(rev)-SUM(spend).  I tried including it as UNION ALL but no luck, and after reading about UNION ALL I understand why.  I'm guessing I need to incorporate a JOIN statement, but not sure where to begin.  Please help me write the proper SQL statement.  </p>