将两个SQL表组合成一个已排序的HTML输出

Question

I have created an example of my two SQL tables. The first requires a simple multiplication to produce the final "Revenue", while the second table stores this value directly as "Bonus". What I want to be able to do, is create a PHP page which allows me to select any employee, and have it produce an HTML table (like the 3rd table in my snippet).

I am uncertain if I can "JOIN" these two tables somehow in an SQL query. I can run two separate queries on each table two produce two separate arrays, but I don't know how to then sort the tables together in order of date.

MONTHLY DATA (sql table)
<table>
  <tr>
    <th>Month</th>
    <th>Employee</th>
    <th>Net Sales</th>
    <th>Com%</th>
  </tr>
  <tr>
    <td>Oct</td>
    <td>Tim</td>
    <td>275,500</td>
    <td>.05</td>
  </tr>
  <tr>
    <td>Oct</td>
    <td>Sara</td>
    <td>219,200</td>
    <td>.06</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Tim</td>
    <td>391,500</td>
    <td>.055</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Sara</td>
    <td>359,800</td>
    <td>.06</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Sara</td>
    <td>116,100</td>
    <td>.10</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Alex</td>
    <td>217,100</td>
    <td>.04</td>
  </tr>
</table>
<br />
<br />BONUS DATA (sql table):
<table>
  <tr>
    <th>Month</th>
    <th>Employee</th>
    <th>Bonus</th>
  </tr>
  <tr>
    <td>Oct</td>
    <td>Tim</td>
    <td>500</td>
  </tr>
  <tr>
    <td>Oct</td>
    <td>Sara</td>
    <td>800</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Tim</td>
    <td>600</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Sara</td>
    <td>950</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Alex</td>
    <td>450</td>
  </tr>
</table>
<br />
<br />View Sara's Monthly Revenue (html output):
<table>
  <tr>
    <th>Month</th>
    <th>Toal Rev</th>
    <th>Source</th>
  </tr>
  <tr>
    <td>Oct</td>
    <td>13,152</td>
    <td>"from sales"</td>
  </tr>
  <tr>
    <td>Oct</td>
    <td>800</td>
    <td>"bonus"</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>33,198</td>
    <td>"from sales"</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>950</td>
    <td>"bonus"</td>
  </tr>
  <tr>
    <td>.</td>
  </tr>
  <tr>
    <td></td>
    <td>48,100</td>
    <td>TOTAL</td>
  </tr>
</table>

</div>

Answer 1

MONTHLY DATA (sql table)

Month    Employee    Net Sales    Com%
Oct      Tim         275,500      .05
Oct      Sara        219,200      .06
Nov      Tim         391,500      .055
Nov      Sara        359,800      .06
Nov      Sara        116,100      .10
Nov      Alex        217,100      .04

BONUS DATA (sql table):

Month   Employee    Bonus
Oct     Tim         500
Oct     Sara        800
Nov     Tim         600
Nov     Sara        950
Nov     Alex        450

You need to pull the data out of the two tables separately and then UNION it together. SQL like the below will pull the data back for you, after that calculating the final total should be striaghtforward.

SELECT
    Month,
    [Total Rev],
    Source
FROM
    (
    SELECT
        Employee,
        Month,
        SUM([Net Sales] * [Com%]) AS [Total Rev],
        'from sales' AS Source
    FROM
        [MONTHLY DATA]
    GROUP BY
        Employee,
        Month
    UNION
    SELECT
        Employee,
        Month,
        SUM(Bonus) AS [Total Rev],
        'bonus'
    FROM
        [BONUS DATA]
    GROUP BY
        Employee,
        Month
    ) AS tbl1
WHERE
    Employee='Sara'
ORDER BY
    Month,
    Source DESC