| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Hierarq SQL
A copy of this was sent to "Miguel Almeida" <jmiguel_at_cet.pt>
(if that email address didn't require changing)
On Tue, 23 Feb 1999 18:59:58 -0000, you wrote:
>Can someone help me?
>
>Table HIEA with:
> A B
> ------- -------
>AB01001 JJ01001
>AA01006 AB01001
>0119023 AA01006
> .... .......
>
>When i execute:
>SELECT A,B FROM HIEA WHERE A NOT IN (SELECT B FROM HIEA) START WITH
>B='JJ01001' CONNECT BY PRIOR A=B;
>Result:
> A B
>------- -------
>0119023 AA01006
>
>However i want:
> A B
>------- -------
>0119023 JJ01001
>
How is it expected for that row, 0119023 JJ01001, to come back when that row does not exist in the database?
lets back up a step, what is the question you are trying to answer -- instead of giving us the 'answer'...
given your sample table and the output, you want to join all A's -- such that A is not in the set of B's in HIEA -- to all B's meeting some criteria. If that is the case, maybe:
SQL> select *
2 from ( select a from hiea where a not in ( select b from hiea )),
3 ( select b from hiea where b = 'JJ01001' )
4 /
A B ------------------------- ------------------------- 0119023 JJ01001
but so is:
select '0119023', 'JJ01001' from dual
/
:)
>How can i obtain it?
>
>Thanks,
>Miguel
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |