13.11. Same Relation A and B, M - 1 match over cols Aid(D),Bid(E)¶
For this case and the next, we will concentrate on a single relation and what we can do with that same relation being the A and B input relations of a Match Join.
Let’s focus on the Creature relation, whose conceptual schema fragment and columns in its relation are as follows:
Find each creature with its idol creature data.
For this case, let’s look at the reflexive relationship. In this case we know from the shape that a Creature can be the idol of many Creatures, but each Creature has one idol Creature. We therefore have a M - 1 situation when matching over idol_creatureId and creatureId. This means we have this precedence chart for the Match join:
SQL Syntax for this reflexive query
In the example below, I chose to make the query look similar to the times from the set operator chapter in the first tab. In the second tab it is shortened. Once you see how the query contains the Times, you will probably prefer the second tab’s syntax.
Note that in the above example, you need to scroll to the right to see all of the columns when paring each Creature with its idol.
Q-4: Which creatures are missing from the result? Why?