| 12/21/2006 4:17:56 PM |
I'm fairly comfortable with SQL, but OPath is new to me. I've looked through the OPath documentation and I've done some searching on the Web, but I haven't been able to find any examples showing how to construct an OPath query which retrieves only objects which have at least one child in a child collection.
I'd like to retrieve records in table 'Parent' which have a record in table 'Child' where Parent has a one-to-many (foreign key) relationship with Child.
The SQL I'm trying to generate would look something like:
SELECT parent_id FROM Parent WHERE EXISTS (SELECT * FROM Child WHERE Child.parent_id = Parent.parent_id)
OR
SELECT DISTINCT parent_id FROM Parent P INNER JOIN Child C ON P.parent_id = C.parent_id
OR
SELECT DISTINCT parent_id FROM Child
OR
SELECT parent_id, COUNT(child_id) FROM Parent P INNER JOIN Child C ON P.parent_id = C.parent_id GROUP BY parent_id HAVING Count(child_id) > 0
Does anyone have any hints?
Thank you,
Bill Daly
|
| 1/5/2007 7:16:03 AM |
Hi Bill,
You can use the EXISTS keyword... but support for it has not made it into a release version of WORM yet. I have a version of 4.2.2.1 with an updated OPath Engine which supports EXISTS and IIF. If you are interested, just send me your email address and I can send it to you.
The current requirement for using EXISTS is that it must start the expression with a relationship traversal and have some sort of test within that.
For example:
OPathQuery<Customer>("EXISTS(Orders[Freight > 100])");
where Orders is the traversal giving the exists context, and Freight > 100 is the test in that context.
In you case, the expression might look something like this:
OPathQuery<Parent>("EXISTS(Children(1 = 1)")
The "1 = 1" part is something of a hack until I can figure out how to get around it. You should be able to have a relationship name in the exists without a test (e.g., EXISTS(Children)) but I haven't had the mental energy yet to figure out what needs to be modified in the parser.
Sorry for the late reply; sort of dropped everything during the holiday season.
- Jeff Lanning
|
| 1/10/2007 6:12:36 PM |
Jeff, can i get a copy of this opath engine with the exists support?
Cheers,
Floyd Price
f.price@componentworkshop.com |
| 3/13/2007 7:56:27 PM |
Hi Jeff,
I am also working with OPath since a while. Some time ago, we had some contact in this forum, therefore I am also interested in the latest version of the OPath Engine
Could you mail it to me
stefan.roesch@wega-informatik.ch
Thanks and regards
Stefan
|