vendredi 23 janvier 2015

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 |

Aucun commentaire:

Enregistrer un commentaire