SQL solution needed

From: Gary Knopp <gknopp_at_ascend.com>
Date: 2000/04/17
Message-ID: <8dfhkh$49a@nntpb.cb.lucent.com>#1/1

I'm trying to develop a system that stores all the parameters for a particular product release. As new releases are introduced, I want to be able to utilize parameters from past products that still apply if the parameter value is still the same ( to avoid record duplication). At any time I want to be able to query a past product and get a list of its parameters
and values.

I have figured out a way to do this with a view using a correlated subquery, however it
is not the most efficient solution. Does anyone have a better idea how I could
accomplish the above. I have enclosed my solution below.

/**********************************************************/

Below is an example of a solution with the expected results for each query.

PRODUCT - holds distinct names of all products PRODUCT_MAP - identifies past product parameters that are inherited on

                                   newer version.
PARAMETER        - contains a list of parameter and values.  If a paramter
value
                                   changes in a new version than a new
record is created for
                                   that new version.

TABLE DATA

---

          PRODUCT                  PRODUCT_MAP
PARAMETER
    ==============       =====================


=========================

    version     name             version    parent_version           name
value         version
    ----------     ----------         ----------    ---------------------
        -------           -------         -----------
          1           Prd_A                1                      1
voltage         10                 1
          2           Prd_B                2                      1
current          20                 1
          3           Prd_C                2                      2
voltage         11                 2
                                                   3                      1
current           21                3
                                                   3                      2
power           231              3
                                                   3                      3


/*************************************************************/

 CREATE VIEW myView (product,name,value)  as
SELECT prd.name, p.name, p.value

FROM     product prd1
                 parameter param1,
WHERE  p.version=
                   (SELECT
                                 max(param2.version)
                     FROM
                                 parameter param2
                     WHERE
                                 param2.name=param1.name and
                                 param2.version in (
                                           SELECT
                                                         map.parent_version
                                           FROM
                                                         product   prd2,
                                                         product_map map
                                           WHERE
                                                         prd2.version =
map.version and
                                                         prd2.name =
prd1.name
                                          )
                    );

/***************************************************************/

   RESULTS from running queries

---

  Select name,value from myView where product = 'Prd_A'

... = 'Prd_B'

... = 'Prd_C'

                       Prd_A                                  Prd_B
Prd_C
                     ========                        ==========
===========
                      name     value                 name     value
name       value
                     --------    --------                -------     -------
-            ---------     ---------
                      voltage     10                   voltage      11
voltage       11
                       current    20                   current       20
current     21

power         231

Received on Mon Apr 17 2000 - 00:00:00 CDT