I need some help with a query. My db design currently contains 3 levels of entities (following principle of grandparent, parent, child).
#1. "GrandParent" and "Parent" are linked via Junction Table
#2. "Parent" and "Grandchild" are linked via Junction Table
In most cases, I have "complete records" where all 3 levels are linked top to bottom. However, in some instances, I may only have records of "scenario" #1... while in other cases I only have those of #2.
To find the missing Grandparent, I use a Left join query... starting from the parent level.
To find the missing Grandchild, I use a Right join query... also starting the the parent level.
Not sure if it's possible, but I'd like to combine both Right and Left join query in a single query (vs. using two queries like right now).
I tried the UNION but that doesn't seem to work. Hopefully I provided enough info in order to get some general ideas as to how I can accomplish it.