Oracle self join

[rawatds]

Hi all,
I have a query where i have to use a Self join on a table
i want to avoid it as it is taking time.

My data is :
Table 1
Column1 column2 column3
x y z
A B C

Table 2
Column1 Column2 Column3 column4
1 2 3 A
4 5 6 A
7 8 9 A
10 11 12 B

Now my requiremntis to get value 3, 6, 9 from table2 in a single query
along with value A , B , c means one row from table1 but 3 rows from
from table2.

I wrote query

Select A.column1, A.column2 , A.column3 , B.column3 ,
C.column3 , d.column4
from Table1 A ,
Table2 B ,
Table2 C ,
Table2 D
where A.column1 = B.column4
AND B.column1 = C.column1
AND B.Column1 = D.column1
AND B.column3 = 3
AND C.column3 = 6
AND D.column3 = 9

Please assist.
Rawat

 

[SantaMufasa]

Rawat,

I doubt you want to use the logic that appears in your SELECT statement, above, for these reasons:

1) You reverse engineered your query to match co-incidental data values in your Table2. If you co-incidental data values change, your query must change.

2) Is it not true that you want the values '3','6', and '9' from Table2 because those rows have in their "column4" a matching value to Table1.Column1, and not because their values are '3','6', and '9'?

3) If Table2 had 35 rows with 'A' in column4, you would need to read from 36 tables, right?

So, let's revisit your business needs. Is it vital to your business needs to present your child data horizontally instead of vertically? If so, please explain in an additional post. If not, then this code should work satisfactorily for you:

Select A.column1, A.column2 , A.column3 , B.column3 
       from Table1 A ,
            Table2 B 
       where A.column1 = B.column4;

Although your results will appear vertically, the code will work properly no matter how many child rows exist per parent row.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:45 (16Feb05) UTC (aka "GMT" and "Zulu"),
@ 09:45 (16Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.