SQL Join in Oracle

Question

I have two tables :TAB1 and TAB2.

TAB1 fields : itemid(p) and description
TAB2 fields : itemid(F) and parentitems.

TAB2 is subitems of TAB1 so I want to retrieve the all items from TAB1 and parent items equivalent item desciption from TAB1.

Please find the below Query.

Select 
    t1.itemid ,
    t1.DESC, 
    t2.parentitems,
    t2.DESC 
from TAB1 t1 left join TAB2 t2 on t1.itemid = t2.parentitems 
where 
    some conditions...

Let me give some sample values..

TAB1:

item   Desc
A1     aa
A2     bb
A3     cc
A4     dd

TAB2:

item   parentitems
A1     A1
A1     A2
A4     A2
A4     A2

How to retrieve parent items equivalent desc from TAB1 ?

Answer 1

I'm not sure what exactly you want, but it sounds like something like this:

Select 
    t1.item item,
    t1.Desc desc1, 
    t2.parentitems pitem
    ,t1_2.Desc desc2
from TAB1 t1 left join TAB2 t2 on t1.item = t2.item
left join TAB1 t1_2 on t2.parentitems = t1_2.item 

Here is a sqlfiddle example

Answer 2

Below is MS SQL Server base query statement.

SELECT TAB2.parentitems, TAB2.itemid, TAB1.description FROM TAB1 RIGHT OUTER JOIN TAB2 ON TAB1.itemid = TAB2.parentitems

---

Good Luck... :)

Chamath Jeevan

Answer 3

you have to use recursives. for more info see here