Re: Compare a table to itself

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 22 Jul 2006 04:29:41 -0700
Message-ID: <1153567781.911902.285120@m73g2000cwd.googlegroups.com>

Newkid wrote:
> 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:
>
> a) If stage id = stage id and stage dependecy = stage dependency then
> display a single row with both Y Y (means, both has same stage)
>
> b) 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

Simple example of a self join, without trying to understand the contents of your view.
SELECT

  TN1.RDT_RO_ID,
  TN1.RDT_PARTNER_RO_ID,
  TN1.RDT_STAGE_ORDER,
  TN2.RDT_RS_ID,
  TN2.RDT_RDS_ID,
  TN2.RDT_STAGE_ORDER

FROM
  ROMAX.TEMPLATE_NEG TN1,
  ROMAX.TEMPLATE_NEG TN2
WHERE
  TN1.RDT_RO_ID=TN2.RDT_RS_ID
  AND TN1.RDT_PARTNER_RO_ID=TN2.RDT_RDS_ID; The above gives an equi-self join on your view where the RDT_RO_ID column in one instance of the view is the same as the RDT_RS_ID in the second instance of the view. Also, RDT_PARTNER_RO_ID in the one instance of the view must be the same as RDT_RDS_ID in the second instance.

---

Using the same example, but changing the WHERE clause: WHERE
  TN1.RDT_RO_ID=TN2.RDT_RS_ID(+)

  AND TN1.RDT_PARTNER_RO_ID=TN2.RDT_RDS_ID(+)
  AND TN2.RDT_RS_ID IS NULL
  AND TN2.RDT_RDS_ID IS NULL;

The above modification will pick up every row from the first instance of the view where there is no matching row in the second instance of the view.

---

Using the same example, but changing the WHERE clause: WHERE
  TN1.RDT_RO_ID(+)=TN2.RDT_RS_ID

  AND TN1.RDT_PARTNER_RO_ID(+)=TN2.RDT_RDS_ID
  AND TN1.RDT_RO_ID IS NULL
  AND TN1.RDT_PARTNER_RO_ID IS NULL;

The above modification will pick up every row from the second instance of the view where there is no matching row in the first instance of the view.

There are also possibilities for UNION, INTERSECT, and MINUS between two SELECT statements, depending on what you need. Experiment with various syntax to find the one that not only meets your needs, but also provides reasonable performance.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jul 22 2006 - 06:29:41 CDT