Home » SQL & PL/SQL » SQL & PL/SQL » Self Join (Oracle 9i, Win XP)
Self Join [message #353783] Wed, 15 October 2008 01:59 Go to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Hello All,

I have one query using Self Joi., as

select a.pno, a.pname, a.psal
from ptemp4 a, (select pno, psal from ptemp4 where pno = 1)b
where a.psal > b.psal
/

But, I want this to be executed as a pure self join, without sub-query, joins or inline views?
I tried it as above by the use of Inline query, but I want it as a purely Self-Join query.

Can anybody help me on this issue?

Thanks and Regards,
-Prachi
Re: Self Join [message #353797 is a reply to message #353783] Wed, 15 October 2008 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:
anacedent wrote on Sun, 14 September 2008 05:07
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

You choose to not follow Posting Guidelines.
We choose to not answer your question(s).


Michel Cadot wrote on Sun, 14 September 2008 06:58
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
In the end, always post your Oracle version (4 decimals).

Regards
Michel


Re: Self Join [message #353803 is a reply to message #353783] Wed, 15 October 2008 02:58 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Thanks for the reply.

Here it is:

Hello All,

I have one query using Self Join, as

SELECT a.pno, a.pname, a.psal
FROM ptemp4 a,
(SELECT pno, psal
FROM ptemp4
WHERE pno = 1)b
WHERE a.psal > b.psal
/

But, I want this to be executed as a pure self join,
without sub-query, joins or inline views?
I tried it as above by the use of Inline query,
but I want it as a purely Self-Join query.

Can anybody help me on this issue, please?

Thanks and Regards,
-Prachi
Re: Self Join [message #353810 is a reply to message #353803] Wed, 15 October 2008 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted, use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Self Join [message #353850 is a reply to message #353783] Wed, 15 October 2008 05:57 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Did you try without Inline view ? If so , what was the output /Error ?

Show us .

Thumbs Up
Rajuvan.
Re: Self Join [message #353861 is a reply to message #353850] Wed, 15 October 2008 06:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming what you're asking for is to do what that query does, but only accessing a single copy of the table, then yes, it can be done:
create table test_002 (col_1 number, col_2 number);

insert into test_002 values (1,4);
insert into test_002 values (2,5);
insert into test_002 values (4,6);
insert into test_002 values (6,7);
insert into test_002 values (8,8);

select col_1,col_2
from  (select col_1
             ,col_2
             , max(case when col_2 =6 then col_1 else null end) over () x
       from   test_002)
where col_1 >= x;

     COL_1      COL_2
---------- ----------
         4          6
         6          7
         8          8
Re: Self Join [message #353905 is a reply to message #353861] Wed, 15 October 2008 09:41 Go to previous message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Hi All,

Thanks for your replies.

I got the solution as below:

select a.pno, a.pname, e.psal
from emp a, emp b
where b.pno = 8
and b.psal < a.psal;

Regards,
-Prachi
Previous Topic: how to achieve Anti pivoting in 10g
Next Topic: Finding empty strings with LIKE
Goto Forum:
  


Current Time: Wed Dec 07 16:53:08 CST 2016

Total time taken to generate the page: 0.05698 seconds