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

If you really want someone to assit you on this shouldn't you have
provided 3 create table statements with insert statements to populate
the tables with a minimum amount of data?

You should also always include the Oracle edition and version since
features are edition/version dependend.

The solution will depend on how your data is orgranized and how the
tables are related. Someone could guess but he or she would be wasting
their time if your design was different from what was expected.

HTH -- Mark D Powell --