What would be your approach to aggregate two tables with all relationship types (1-1, 1-M, M-1, M-M,
Simple question for you SQL experts!
I have two tables (Budget and Actual) that I want to aggregate into one. The records' relationship is: 1-1, M-1, 1-M, M-M, 0-1, and 1-0.
Below is an example. How would you merge the data ? Join, Union?
Budget Table | Actual Table | ||
Dept | Hrs | Dept | Hrs |
| A | 1 | A | 1 |
| B | 1 | B | 2 |
| B | 1 | C | 1 |
| C | 2 | C | 1 |
| D | 1 | D | 1 |
| D | 2 | D | 1 |
| E | 1 | F | 1 |
Total | 9 | Total | 8 |
I need the results to be like below (single table/output):
Dept | Budget | Actuals |
| A | 1 | 1 |
| B | 2 | 2 |
| C | 2 | 2 |
| D | 3 | 2 |
| E | 1 | 0 |
| F | 0 | 1 |
Total | 9 | 8 |
1