Re: A query without a sub-query: can we do it?

In our last gripping episode Atta ur-Rehman <atta707_at_my-deja.com> wrote:
> dear all,
>
> i've following table named lab with user_id, lab_id as the PK:
>
> select * from lab;
>
> USER_ID LAB_ID
> ---------- ----------
> 2 1
> 2 2
> 2 3
> 9 3
> 9 4
> 9 5
> 9 6
>
> now i wanna see all the labs where user_id 2 is a member except for
 the
> labs where 9 is a member as well, then i'd say:
>
> select * from lab
> where user_id = 2
> and lab_id not in (select lab_id from lab where user_id = 9);
>
> that results in:
> USER_ID LAB_ID
> ---------- ----------
> 2 1
> 2 2
>
> and rightly so. user_id 2 belongs to lab 1, 2 and 3. lab 3 has not
 been
> selected 'cas user 9 is a member of this lab. great!
>
> now my question is: can i write the same query without having to use
> the sub-query as i've done above? if yeah, how? theoratically speaking
> is there *always* an equivilant SQL statement for every query that
 uses
> with sub-query?
>
> thanks in advance for any help.
>
> :) ATTA
>
> --
>
> getting the meanin' of data...
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

This sounds like homework to me, and I'm reluctant to offer a solution knowing you have far more time to decipher this problem than those of us who work for a living. Be that as it may you can try this:

select lab_id
from lab
where user_id = 2
minus
select lab_id
from lab
where user_id = 9

No formal subquery there.

Also, since you are a student it would behoove you to use correct verbiage in written communication with others. Your post would not be tolerated in the 'real' world.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.