Query to pull sum from 2 different tables, is this possible in one query?

Question

Thanks for all the help so far, now I just ran into a major problem!

I have 2 tables that contains daily commissions, and for each day, I have to SUM the daily totals from each table. Daily limit is maxed at $100, so anything over $100 for the day is $100.

Employee (employeeID INT PK, totalCommisions INT )

Now the 2 tables that contain comission data are:

ProductSales (productID INT, employeeID INT, commission INT, Created DATETIME)

Referrals (referalID INT, employeeID INT, commission INT, Created DATETIME).

An employee can have 10 product sales in a day, and 50 referrals. The key point is, the SUM of the commission for both productSales and Referrals, if over $100, is set to $100 (i.e. daily $100 max).

The query I need is to update the Employee tables TotalCommission column with the total commisions earned for each employee (entire period, no date range).

The query will have to use a CASE statement since the daily total cannot be over $100 (SUM of commissions from ProductSales and Referrals for the day).

Previous Question: http://stackoverflow.com/questions/1658421/query-to-get-sum-of-an-employees-commission/1658468#1658468

Answer 1

This can be easily achieved using subqueries. You write simple query to do the thing with one table, do the same with another and then join those two and do what you have to do in select part. The query will look sth like this:

SELECT (here goes the case and summing) FROM (query from ProductSales) OUTER JOIN (query from Refferals) ON (...)

Answer 2

Maybe something like this -

select employeeID, Year(Created), Month(Created), Day(Created),
case
  WHEN sum(Commission) > 100 THEN 100
  ELSE sum(Commission)
END
from
(
  select employeeID, Created, commission from ProductSales
  UNION ALL
  select employeeID, Created, commission from Referrals
) Commissions
group by employeeID, Year(Created), Month(Created), Day(Created)