JOIN multiple tables with WHERE clauses
I have a master table (Project List) along with several sub tables that are joined on one common field (RecNum). I need to get totals for all of the sub tables, by column and am not sure how to do it. This is a sample of the table design. There are more columns in each table but I'm showing a sampling of the column names and values to get an idea of what to do.
Project List | RecNum | | 6 | WeekA | RecNum | UserName | Day1Reg | Day1OT | Day2Reg | Day2OT | Day3Reg | Day3OT | | 6 | JustMe | 1 | 2 | 3 | 4 | 5 | 6 | | 6 | NotMe | 1 | 2 | 3 | 4 | 5 | 6 | WeekB | RecNum | UserName | Day1Reg | Day1OT | Day2Reg | Day2OT | Day3Reg | Day3OT | | 6 | JustMe | 7 | 8 | 1 | 2 | 3 | 4 | | 6 | NotMe | 7 | 8 | 1 | 2 | 3 | 4 |
So the first query should return the complete totals for both users, like this:
| sumDay1Reg | sumDay1OT | sumDay2Reg | sumDay2OT | sumDay3Reg | sumDay3OT | | 16 | 20 | 8 | 12 | 16 | 20 |
The second query should return the totals for just a specified user, (WHERE UserName = 'JustMe') like this:
| sumDay1Reg | sumDay1OT | sumDay2Reg | sumDay2OT | sumDay3Reg | sumDay3OT | | 8 | 10 | 4 | 6 | 8 | 10 |
It is a bad idea to have multiple tables that store the same kind of data; you should have a single Weeks table that has an additional column for the week.
Anyway, the easiest way to handle this table structure is to combine the tables first:
SELECT SUM(Day1Reg) AS sumDay1Reg, SUM(Day10T) AS sumDay10T, ... FROM (SELECT * FROM WeekA UNION ALL SELECT * FROM WeekB) WHERE userName = 'JustMe' -- optional