Home » SQL & PL/SQL » SQL & PL/SQL » Correlated Subquery ?
icon5.gif  Correlated Subquery ? [message #220414] Tue, 20 February 2007 10:39 Go to next message
jilpangs
Messages: 16
Registered: April 2005
Junior Member
Hi,

I need to find particular column value(ColActual) and
get its replacement item (colReplace) AND
if ColReplace is available as ColActual
then i need to find the colReplace for that record and display it.

For Example

Sample data

ColActual ----- ColReplace
Item1---------- Item4
Item4---------- Item10


My output should be "Item 10" based on above sample data , because
1) First i will find Item 1 and find its replace which is Item4.
2) Then i'll check for Item4 in "ColActual" which is present , so then i will have to
get its ColReplace value which Item 10.


Problem.....
i have situations like following...check Item1 referring back to itself.

ColActual ----- ColReplace
Item2---------- Item6
Item3---------- Item10
Item1---------- Item4
Item6---------- Item9
Item11---------- Item14
Item4---------- Item1

My requirement now is to identify ONLY such recurrsive data.I need
both records in my output (if possible in ordered fashion).
For example , for the given data the output should be ,

ColActual ----- ColReplace
Item1---------- Item4
Item4---------- Item1

Is this possible in one query. I cant write a procedure for this as i dont have access to create procedures.

PLS HELP .. Thanks
Re: Correlated Subquery ? [message #220422 is a reply to message #220414] Tue, 20 February 2007 11:19 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
Is that ok? I am not sure if it is really what you are asking.

CREATE TABLE pm_test
(colactual VARCHAR2(20),
colreplace VARCHAR2(20));

INSERT INTO pm_test VALUES('Item2','Item6');
INSERT INTO pm_test VALUES('Item3','Item10');
INSERT INTO pm_test VALUES('Item1','Item4');
INSERT INTO pm_test VALUES('Item6','Item9');
INSERT INTO pm_test VALUES('Item11','Item14');
INSERT INTO pm_test VALUES('Item4','Item1');
INSERT INTO pm_test VALUES('Item15','Item12');
INSERT INTO pm_test VALUES('Item12','Item15');
COMMIT;

SELECT pt1.*
FROM pm_test pt1,
pm_test pt2
WHERE pt1.colactual = pt2.colreplace
AND pt1.colreplace = pt2.colactual;
Re: Correlated Subquery ? [message #220425 is a reply to message #220422] Tue, 20 February 2007 12:08 Go to previous messageGo to next message
jilpangs
Messages: 16
Registered: April 2005
Junior Member
Thanks ..that solves 90% of my requirement.

i also need the output in sorted order...

Sample data

ColActual ----- ColReplace
Item2---------- Item6
Item3---------- Item10
Item1---------- Item4
Item6---------- Item2
Item11---------- Item14
Item4---------- Item1

Output should be(next row should start with matching replace column)
ColActual ----- ColReplace
Item2---------- Item6
Item6---------- Item2
Item1---------- Item4
item4---------- Item1
Re: Correlated Subquery ? [message #220428 is a reply to message #220414] Tue, 20 February 2007 12:24 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
What if you have

ITEM1 ITEM4
ITEM4 ITEM3
ITEM3 ITEM1
Re: Correlated Subquery ? [message #220429 is a reply to message #220428] Tue, 20 February 2007 12:29 Go to previous message
jilpangs
Messages: 16
Registered: April 2005
Junior Member
I'll be checking for only one level...so i can ignore
3rd record.
Previous Topic: to drop tables
Next Topic: Horizontal Output from ORACLE SQL
Goto Forum:
  


Current Time: Sun Dec 04 00:38:53 CST 2016

Total time taken to generate the page: 0.07685 seconds