Compare a table to itself

From: Newkid <shyamspandey_at_gmail.com>
Date: 21 Jul 2006 23:44:14 -0700
Message-ID: <1153550654.230978.318640@h48g2000cwc.googlegroups.com>

Hi there!

Please guide me how to compare a table to itself. Problem is like this:

There is a service and for each service there are many templates. Each template has many stages ( standard and customs). There are two milestones say A and B. Now, one needs to compare for a particular service and particular template how many stages A has which B hasn't and vice versa or both has same stages.

So far, I extracted all the information and create a view. Now my concern is how to compare view to itself and define 'Y' and 'N' under respective position.

Beside this one needs to take care couple of conditions:

1. If stage id = stage id and stage dependecy = stage dependency then display a single row with both Y Y (means, both has same stage)
2. If stage id = stage id and stage dependecy NOT EQUAL to stage dependency then display two rows i,e, Y/N and N/Y

For instance, VIEW for your reference:

CREATE OR REPLACE FORCE VIEW "ROMAX"."TEMPLATE_NEG" ("RDT_RO_ID",
"RDT_PARTNER_RO_ID", "RDT_RS_ID", "RDT_RDS_ID", "RDT_STAGE_ORDER",
"RDT_DEPENDENCY", "RDT_REC_INTERVAL", "RDT_ACTION_BY", "RDS_NAME",
"RDS_RO_ID") AS

  Select
"RDT_RO_ID","RDT_PARTNER_RO_ID","RDT_RS_ID","RDT_RDS_ID","RDT_STAGE_ORDER","RDT_DEPENDENCY","RDT_REC_INTERVAL","RDT_ACTION_BY","RDS_NAME","RDS_RO_ID"
from (Select A.RDT_RO_ID,A.RDT_PARTNER_RO_ID,

A.RDT_RS_ID,A.RDT_RDS_ID,A.RDT_STAGE_ORDER,A.RDT_DEPENDENCY,
A.RDT_REC_INTERVAL,A.RDT_ACTION_BY,
B.RDS_NAME,B.RDS_RO_ID from ROMAX_DELIVERY_TEMPLATE A left join

ROMAX_DELIVERY_STAGE B
on A.RDT_RDS_ID = B.RDS_ID) order by RDT_Stage_order;

Hope I'm making some sense...

Would appreciate prompt response!
Shyam Received on Sat Jul 22 2006 - 01:44:14 CDT