Outer Joins, decode, case combinations : Anyone ?

From: <aroy.dba_at_gmail.com>
Date: 17 May 2006 03:35:56 -0700
Message-ID: <1147862156.013763.27840@38g2000cwa.googlegroups.com>

I have a query that shows a result-set in which shows some columns including one on which the result gets sorted out.

We want to have three fields in the result set:

1. GroupName (from table1)
2. UsrName (from table2)
3. Sub-GroupName (from table3)

We have three groups(GrpA, Grp2, Grp3)
*GrpA has a member A
*GrpB has a member B
*GrpC has a member C

*GrpC is sub-group of GrpB
*GrpB is sub-group of GrpA

( There is a table that stores who is sub-group of whom. )

We want the display to look like this :

---

 GroupName UsrName Sub-GroupName

---

 GrpA             A
 GrpA             B                grpB
 GrpA             C                grpC

The GrpA with '*' in the column GroupName is not the actual GroupName value for the sub-group, that goes to column three. (How to apply these conditions ? [for Main group the actual entry and for sub-group psuedo-entry ] and How to make a pseudo-entry in GroupName Column for a record which is a subgroup but has this same column-name ? )

But it has to be shown just like that for the sub-groups. So effectively, Actual GroupName of the sub-group members go to the Sub-GroupName column only and not to the name of the grpName. (The actual value of the GrpName for the sub-group query, goes to the sub-group column. How to go about it !)

Possibly combination of Outer Joins, decode, case is to be used but we need to check that up.

I've tried to keep the picture simple. Plz let me know if more details are required.

Amit Received on Wed May 17 2006 - 05:35:56 CDT