Peter,
You have defined your relationships like this:
T1 ----< T2 ----< T3
T1 ----< T4
Given this, T3 and T4 have a many-to-many relationship. You would not define a relationship between T3 and T4 directly, because one already exists, going through T1 and T2.
However, this isn’t really the case, is it, because what you didn’t mention below, but which is implied in your table definitions, is:
T4 ----< T3
I am assuming this because you say T4’s PK (primary key) is Key1+Key3, while T3’s PK is Key1+Key2+Key3.
This might lead you to define a relationship between T3 and T4 like this:
T3.Key1 = T4.Key1 AND T3.Key3 = T4.Key3
However, this will introduce loops. Suppose you wrote a query pulling data from T1 and T3. There are now two ways to get from T1 to T3: T1 + T2 + T3, and T1 + T4 + T3. You can resolve these loops in BO through contexts or aliases, but in this particular example there may be an easier way using a new feature in BOv4 called “shortcut joins”. I won’t explain what a shortcut join is, you can read the online help for that, but I’ll show you how it will help in this scenario:
Suppose we make our T3 + T4 join defined above a shortcut join. If we pull data from all four tables, the shortcut join will be ignored – it isn’t needed, because the “normal” joins are sufficient to link all of the tables together. Your WHERE clause will look something like this:
Where
T1.Key1 = T2.Key1 AND
T2.Key1 = T3.Key1 AND – NOTE: You left this condition out of your example below
T2.Key2 = T3.Key2 AND
T1.Key1 = T4.Key1
However, this does not guarantee that T3.Key3 = T4.Key3, which you want to enforce. (Actually, it says T4 >—< T3, instead of T4 ----< T3.) So, the T3 + T4 join is probably a poor choice for a shortcut.
Instead, try setting up the T1 + T4 join as a shortcut, and make the T3 + T4 join a normal join. Now the same query will ignore the T1 + T4 join, which is unnecessary anyway if you are going through T2 and T3. [Why? Because T1 is joined to T4 through Key1, but T2 and T3 have Key1 too, so they are just as good for joining T4 as T1 is.] Now your WHERE clause will look something like this:
Where
T1.Key1 = T2.Key1 AND
T2.Key1 = T3.Key1 AND
T2.Key2 = T3.Key2 AND
T3.Key1 = T4.Key1 AND
T3.Key3 = T4.Key3
So what is the advantage of a shortcut join? The advantage is, if you do not pull data from T3, normally you would have to include T3 in your FROM and WHERE clauses anyway, in order to get from T1 or T2 to T4. But with a shortcut join, if you do not pull data from T3, you have an alternate “path” you can follow. So, for example, if you just pulled data from T1 and T4, your WHERE clause would look like:
Where
T1.Key1 = T4.Key1
If you just pulled data from T2 and T4 your WHERE clause would look like:
Where
T1.Key1 = T2.Key1 AND
T1.Key1 = T4.Key1
You could simplify this by creating ANOTHER shortcut join between T2 and T4: T2.Key1 = T4.Key1. Then your WHERE clause would simply be:
Where
T2.Key1 = T4.Key1
And, you could also create a shortcut join between T1 and T3, so that if they are the only two tables in a query, you will not have to go through T2:
Where
T1.Key1 = T3.Key1
Now we’ve defined shortcut joins between T1+T2, T1+T3, and T1+T4. Basically, what we’re doing here is saying that, since T1’s PK is Key1, and since every other table in the database contains Key1 as part of their PK, every other table can be joined back to T1 directly (if no other path can be found without adding more tables to our FROM clause).
This is not the only solution. For example, the T2+T3 join could have been made the shortcut, instead of the T1+T4 join.
You see, the underlying problem is not T4, it is T3. if you take T3 out of your database, the relationships would look like this:
T1 ----< T2
T1 ----< T4
While this still leaves T2 and T4 with a many-to-many relationship, at least there are no loops to contend with. But once you introduce T3, you have problems. Why? Because T3 has two different parents: T2 and T4. T2 is a parent because T3 contains Key1 and Key2, as you have already said. But T4 is an equally legitimate parent because T3 contains Key1 and Key2. So the nature of your database creates loops which have to be dealt with.
Hope this makes sense. I just wrote it as it came to me, so it may not be the most coherent email ever…
Erich Hurst
Compaq Computer Corporation
(281) 514-9445
Erich.Hurst@Compaq.com
From: Peter Notenbaert [SMTP:peter.notenbaert@AQUAFIN.BE] Sent: Tuesday, June 16, 1998 10:21 AM
If I have 5 tables :
T1
Key1
T2 :
Key1
Key2
T3 :
Key1
Key2
Key3
Atribute1
T1 ----< T2 ----< T3
T4 :
Key1
Key3
Atribute2
T1 ----< T4
T5
Key3
and I want BO to generate the following SQL (or another with the same results):
Select
T2.Key2,
T3.Key3,
T3.Atribute1,
T4.Atribute2
From
T1, T2, T3, T4
Where
T1.Key1 = T2.Key1 AND
T2.Key2 = T3.Key2 AND
T1.Key1 = T4.Key1 AND
T3.Key3 = T4.Key3
How do I have to define the relations between the tables? The last one (T3.Key3 = T4.Key3) is the one I have trouble with, I would like it to appear every time T3 and T4 are selected.
Thanks,
Peter Notenbaert
Listserv Archives (BOB member since 2002-06-25)