Log In
Register
Home
November 27, 2018
Offline
Hi - I have been trying to figure this out for quite some time...I feel like I have writers block, so I hope someone can help!!
I have two sets of data that I want to join, and see what matches and is different from each set.
I have Products that I need to compare to the standard Templates to determine what was built to the standard.
| Products | Templates | |||||||
| ProdID | TemplateID | RuleID | Rules | TemplateID | RuleID | Rules | ||
| A1 | X | 1 | A | X | 1 | A | ||
| A1 | X | 2 | B | X | 2 | B | ||
| A1 | X | 3 | D | X | 3 | E | ||
| B1 | Z | 1 | A | Y | 1 | A | ||
| B1 | Z | 3 | E | Y | 2 | C | ||
| B1 | Z | 7 | H | Y | 5 | F | ||
| Z | 1 | A | ||||||
| * Rules are manually built on Products, and I want to know where they built something non-standard | Z | 3 | E | |||||
| Z | 5 | F | ||||||
| Z | 6 | G | ||||||
| * Templates define the standard rule set | ||||||||
I have 4 scenarios, all of which are valid:
- Products have Rule IDs that match and the Rules match
- Products that have Rule IDs that match and the Rules do not match
- Products that have Rule IDs not in my standard Templates
- Rule IDs which are in my standard Templates but not in my Product
I've tried full outer joins, but filling in the Product or Template is a challenge because I have more templates than may be in my Product table. I have used new fields to look at matches then filtered out what doesn't match and then joined back to the templates, but just cannot get the Product associated with the missing rules!
Does anyone have a suggestion/solution to try? I've included a file with these two tables. I'm hoping there is a simple, elegant solution that is staring me in the face, and you will enlighten me with your genius!!
Thank you so much - Beth
July 16, 2010
Offline
Hi Beth,
You need 4 separate queries here, one for each scenario. The join type will differ depending on the scenario. Have you seen this post which explains the different join types and when to use them?
Mynda
1 Guest(s)
