I have a table structure consisting of 3 tables.
Table 1. MasterTable consists of 2 columns, ObjectID, ObjectName
Table 2. DetailTable consists of 3 columns, ObjectID, PropertyName, Value
Table 3. PropertyTable consists of 1 column PropertyNames
So if the objects in question is for examle a ball with properties Size and Color, info is stored about them as
Table 1
ObjectID ObjectName
1 BigRed
2 SmallBlue
--------------------------
--------
Table 2
ObjectID PropertyName Value
1 Size Big
1 Color Red
2 Size Small
2 Color Blue
--------------------------
----------
-
Table 3
PropertyNames
Size
Color
--------------------------
----------
-
And i need to create a view from this that would look like
NeededView
ObjectID ObjectName Size Color
1 BigRed Big Red
2 SmallBlue Small Blue
--------------------------
----------
----------
----------
----------
----------
--
Properties will likely be added and removed quite often, and every time i will need to recreate the view to display the current set of properties.
So far the only version i have been able to come up with has every property treated like this:
SELECT m.ObjectID, m.ObjectName,
(SELECT d.Value WHERE d.ObjectID = m.ObjectID AND d.PropertyName = "Size") as Size,
(SELECT d.Value WHERE d.ObjectID = m.ObjectID AND d.PropertyName = "Color") as Color
FROM MasterTable m INNER JOIN
DetailTable d ON m.PBXID = d.PBXID
however in practise the object is going to have around 50 properties, so i would be doing 50 select statements to compile every row of this view.
Is there a more gracious way to accomplish this?
Start Free Trial
