| SQL GURUS [message #8471] |
Fri, 22 August 2003 16:15  |
sql gurus
Messages: 22
Registered: May 2002
|
Junior Member |
|
|
i have 2 tables.
Table X
item seq
104289 787492
106375 1120731
66692 98798
Table Y
item seq
106375 787492
66692 787492
66694 787492
66736 787492
156817 1120731
156905 1120731
151931 1120731
45966 1120731
45969 1120731
151935 1120731
here is the puzzle.
i have to first get the item from X and get its corresponding seq ( ex. item=104289 seq=787492)
then find the seq 787492 in table Y and get all items
corressponding to that seq. and search all items in table Y and get those seq's. like that till there is no match.
In a way it is a recurrsive sql.
Is there a way. Todd Barry can you help?
|
|
|
|
|
|
|
|
| Re: SQL GURUS [message #8475 is a reply to message #8471] |
Fri, 22 August 2003 18:26  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please click on the link below for the section of Oracle on-line documentation on hierarchical queries:
In the example below, I have used an additional table. This is necessary in 8i, because 8i does not support join conditions in hierarchical queries. However, in 9i, you could just use the syntax for the creation on table z as an in-line view.
SQL> -- test tables and data:
SQL> SELECT * FROM x
2 /
ITEM SEQ
---------- ----------
104289 787492
106375 1120731
66692 98798
SQL> SELECT * FROM y
2 /
ITEM SEQ
---------- ----------
106375 787492
66692 787492
66694 787492
66736 787492
156187 1120731
156905 1120731
151931 1120731
45966 1120731
45969 1120731
151935 1120731
10 rows selected.
SQL> --
SQL> --
SQL> -- create additional table using outer join
SQL> -- (necessary in 8i, but could be in-line view in 9i):
SQL> CREATE TABLE z
2 AS
3 SELECT x.item AS x_item, x.seq AS x_seq,
4 y.seq AS y_seq, y.item AS y_item
5 FROM x, y
6 WHERE x.seq = y.seq (+)
7 /
Table created.
SQL> SELECT * FROM z
2 /
X_ITEM X_SEQ Y_SEQ Y_ITEM
---------- ---------- ---------- ----------
66692 98798
104289 787492 787492 106375
104289 787492 787492 66692
104289 787492 787492 66694
104289 787492 787492 66736
106375 1120731 1120731 156187
106375 1120731 1120731 156905
106375 1120731 1120731 151931
106375 1120731 1120731 45966
106375 1120731 1120731 45969
106375 1120731 1120731 151935
11 rows selected.
SQL> --
SQL> --
SQL> -- hierarchical query:
SQL> COLUMN hierarchy_ FORMAT A15
SQL> SELECT LPAD (LEVEL, LEVEL * 2) AS hierarchy_,
2 x_item, x_seq, y_seq, y_item
3 FROM z
4 START WITH x_item = 104289
5 CONNECT BY PRIOR y_item = x_item
6 /
HIERARCHY_ X_ITEM X_SEQ Y_SEQ Y_ITEM
--------------- ---------- ---------- ---------- ----------
1 104289 787492 787492 106375
2 106375 1120731 1120731 156187
2 106375 1120731 1120731 156905
2 106375 1120731 1120731 151931
2 106375 1120731 1120731 45966
2 106375 1120731 1120731 45969
2 106375 1120731 1120731 151935
1 104289 787492 787492 66692
2 66692 98798
1 104289 787492 787492 66694
1 104289 787492 787492 66736
11 rows selected.
</pre?
|
|
|
|
|
|
| Re: SQL GURUS [message #8498 is a reply to message #8492] |
Mon, 25 August 2003 13:35 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
What version of Oracle are you using? Which methods you can use is dependent upon your Oracle version. For example, if you have Oracle 9i, one option would be to use an in-line view as in the example below.
COLUMN hierarchy_ FORMAT A15
SELECT LPAD (LEVEL, LEVEL * 2) AS hierarchy_,
x_item, x_seq, y_seq, y_item
FROM (SELECT x.item AS x_item, x.seq AS x_seq,
y.seq AS y_seq, y.item AS y_item
FROM x, y
WHERE x.seq = y.seq (+))
START WITH x_item = 104289
CONNECT BY PRIOR y_item = x_item
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
