Semi-Comple Entity Query

Jeffrey Sullivan

Question

Howdy all, I have a little bit of a complicated query across a set of entities that I can't figure out how to get to work in the Agile Platform.

We have three entites, let's call them Foo, Bar, and Baz. (I've attached an entity diagram to clarify.)

Foo is a parent record, and it can have 0, 1, or 2 associated Bar records.

Bar records have one of two types: Top and Bottom. There will be at most one Bar record of each type for a given Foo.

Baz records are a type table to define the type of Bar records.

Foo and Baz have no foreign key, but Bar has one each pointing to a specific Foo and a specific Baz.

I want to get a selection that lists each foo, and its associated Bar(s) like so:

Foo, Bar ("Top"), Bar ("Bottom")

However, other valid scenarios are:

Foo, Bar ("Top"), NULL
Foo, NULL, Bar ("Bottom")
Foo, NULL, NULL

I can create a query in SQL that gives me what I want:

SELECT * FROM tblFoo F
LEFT OUTER JOIN tblBar R1 ON F.nFooID = R1.nFooID AND R1.nBazID = 1
LEFT OUTER JOIN tblBar R2 ON F.nFooID = R2.nFooID AND R2.nBazID = 2

But I can't seem to get a query working in the platform.

I've attached a sample eSpace with static entities that demonstrated my problem. Just look at the query "joinQuery" in the preparation of Webscreen1.

joinExample.oml

05 May 2010

Jeffrey Sullivan

Here's the sql example.

JoinExample.sql

05 May 2010

Jeffrey Sullivan

And here's the ERD. (BTW, any way to attach multiple files to a post?)

erd.png

05 May 2010

Tiago Simões

Staff

Hi Jefferey,

I think you can only do outer joins in simple queries when the join condition is simple (e.g. Bar.FooID = Foo.Id).
To get what you are looking for you will need an advanced query like this:

(Regarding forum posts you can only attach one file to a post but you can add several images to it.)

Cheers,
Tiago Simões

05 May 2010

Jeffrey Sullivan

Thanks, Tiago.

Is there any way to reference named instances of static entities in an advanced query?

05 May 2010

Tiago Simões

Staff

I believe you would need to send those in parameters.

06 May 2010

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines