Re: Subquery workaround

This should work fine for you:

select unique o.*
from user_node u, node_parent p, node o, user_node u2 where u.user_id = '1'
and p.node_id = u.node_id
and o.node_id = p.node_id
and u2.node_id (+) = p.parent_node_id
and (u2.node_id is NULL or

   u2.user_id <> '1')

Email me directly for any additional info.

JP

adimayuga_at_yahoo.com (Adrienne) wrote in message news:<ee790b43.0204251658.3d7f90e8_at_posting.google.com>...
> I'm using EJB's which provides its own query language (with
> limitations of course). Since ejb-ql is so new, it currently does not
> allow subqueries. We could work w/ Java and loop through many times
> to find our results - but performance wise -> this is not preferred.
> Here is our SQL statement -> can anyone think of an equivalent method
> NOT using subqueries or views?
>
> select unique o.*
> from user_node u, node_parent p, node o
> where u.user_id = '1'
> and p.node_id = u.node_id
> and o.node_id = p.node_id
> and p.parent_node_id not in (select node_id from user_node where
> user_id = '1'
>
> Thanks in advance for any help you can provide!
Received on Fri Apr 26 2002 - 13:47:21 CEST