SQL solution needed
Date: Mon, 17 Apr 2000 13:29:06 -0400
Message-ID: <8dfhl1$49d_at_nntpb.cb.lucent.com>
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 - 19:29:06 CEST