I have two queries from two different sources. There are a couple overlapping columns from each that i'm attempting to use to combine them into one table. Basically I need all the info from both in order to do some comparison. For the sake of discretion, here are sample tables that convey what I'm trying to do:
Recipe Table - Used to quantify how much is supposed to be used for each product
| Product | Ingredient | Required |
| Cake | Chocolate | 5 |
| Cake | Egg | 1 |
| Cake | Flour | 3 |
| Cake | Sugar | 1 |
| Cake | Water | 0.5 |
| Muffin | Cinnamon | 0.1 |
| Muffin | Egg | 2 |
| Muffin | Flour | 4 |
| Muffin | Sugar | 2 |
| Muffin | Water | 0.5 |
Actual Use Data - what the new baker actually used by invoice
| Invoice | Product | Ingredient | Actual |
| A | Muffin | Egg | 3 |
| A | Muffin | Sugar | 2 |
| A | Muffin | Water | 0.5 |
| B | Cake | Chocolate | 5 |
| B | Cake | Egg | 1 |
| B | Cake | Flour | 1 |
| C | Muffin | Chocolate | 2 |
| C | Muffin | Sugar | 1 |
| C | Muffin | Water | 1 |
End Result I'm looking for:
| Invoice | Product | Ingredient | Actual | Required |
| A | Muffin | Cinnamon | 0 | 0.1 |
| A | Muffin | Egg | 3 | 2 |
| A | Muffin | Flour | 0 | 4 |
| A | Muffin | Sugar | 2 | 2 |
| A | Muffin | Water | 0.5 | 0.5 |
| B | Cake | Chocolate | 5 | 5 |
| B | Cake | Egg | 1 | 1 |
| B | Cake | Flour | 1 | 3 |
| B | Cake | Sugar | 0 | 1 |
| B | Cake | Water | 0 | 0.5 |
| C | Muffin | Chocolate | 2 | 0 |
| C | Muffin | Cinnamon | 0 | 0.1 |
| C | Muffin | Egg | 0 | 2 |
| C | Muffin | Flour | 0 | 4 |
| C | Muffin | Sugar | 1 | 2 |
| C | Muffin | Water | 1 | 0.5 |
So, I'm trying to create rows for each of the following:
1. Matching data in both tables
2. Ingredients not used that are in the recipe (Actual = null and Required = something)
3. Ingredients used that are not in the recipe (Required = null and Actual = something)
I've been able to create #1 & #3 with a left outer join. I've tried multiple combinations of joins and anti-joins to create #2, but have been unsuccessful. No matter what I try, i can't associate an invoice to something with no Actual data. How can I accomplish this?
