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

ATTA,
  try this

select * from lab
where user_id = 2
MINUS
select L1.* from lab L1, lab L2
where L1.user_id = 2
and L1.lab_id = L2.lab_id
and L2.user_id = 9

Paolo

In article <8vgodn$p1h$1_at_nnrp1.deja.com>,   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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 22 2000 - 11:08:49 CST