Given the following relations/tables...

Table1.PKey 1:n Table2.FK Table3.FK 1:1 Table1.Pkey

In the database, there is not a defined relation between Table2.FK and Table3.FK, but there is an implied one. If you wanted the information from Table2 and Table3 you could get that without going through table1.

select * from Table2 join Table3 on Table2.FK=Table3.FK

However, in LLBLGen to achieve this, I am having to provide the relations in such a way the following is the result query:

select * from Table2 join Table1 on Table2.FK=Table1.Pkey join Table3 on Table3.FK=Table1.Pkey

Anyway I can get around having to involve Table1 in this operation? (just trying to optimize the queries generate). I tried setting up a relation, but it only lets me relate to Primary Keys - so cannot setup Table2 -> Table3 relationship.

One way I can think of is to define your relationship programically.

So you could define something like EntityRelation relation = new EntityRelation(Table1Fields.FK, Table3Fields.FK, RelationType.OneToOne);

EntityRelation relation = new EntityRelation(Table3Fields.FK, Table1Fields.FK, RelationType.OneToMany);

Then use this relationship when defining your filter.

Doesn't use the designer, but maybe it will give you the result that you are looking for.

Yeah, that might would give the result.

I guess the designer should let use pick non-primary keys when defining additional relationships. Would that be possible Otis (in some future enhancement build)?

Wayne Said:

I guess the designer should let use pick non-primary keys when defining additional relationships. Would that be possible Otis (in some future enhancement build)?

The Designer lets you define relations on Unique Constraints too.

Maybe you can tell me how.

I have TableB.FK 1:1 TableA.PK - so i have a UC on TableB.FK I have TableA.PK 1:n TableC.FK

Now, I want to make a relation between TableB.FK and TableC.FK

When you try to do this you pick an entity to start with and an entity it relates to. The designer then has a "Foreign Key Side"/"Primary Key Side" drop down.

No matter what you do, it select the Primary Key of either the Entity you are relating from or the Entity you are relating to - there is no way to change it (that I know of/see)

Wayne

Oh Yes, I was mistaken. Please refer to the following threads: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5858 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3285 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3459