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 |

Answers


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

Need Your Help

Table locking in ADO.NET

c# ado.net locking

is there a simple way to implement table locking in ADO.NET (without beeing dependend on a specific DBMS-feature)? I'm currently using TransactionScopes, but I'm not quite sure, whether they lock the

“Real” and non-embedded use of Ruby, Python and their friends

python ruby perl scripting

So I'm aware of the big ammount of general-purpose scripting languages like Ruby, Python, Perl, maybe even PHP, etc. that actually claim being usable for creating desktop applications too.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.