Very Computer

I have three tables

1.Equipment

EquipmentID  EquipmentName     Manufacturer
1            Aircoditiner      LG
2            Refregerator      Whirlpool
3            Router            Cisco

2. Attributes

AttributeID AttributeName
1.          Color
2.          YearofManufacture

3. EquipmentAttributes

EquipmentId   AttributeId  Value
1                1         White
1                2         1998
2                1         Red
2                2         2000
3                1         Black
3                2         2001

I want to get a result like this

EquipmentID  EquipmentName     Manufacturer   Color     YearofManufacture
1            Aircoditiner      LG             White     1998
2            Refregerator      Whirlpool      Red       2000
3            Router            Cisco          Black     2001

How can i write a query to get a result set like this connection the above
three tables ?
I cant add the Attributenames to the Equipment table coz the attribute name
is a variable. A new attribute can be added later. Please help

Thanks in Advance
Baiju

Your table tables are denormalized. you can very well run
following query so far you are not adding any
rows to  attributes table. But if this is going to be your
table forever you may have to consider normalizing
your schema. Or you may have to write a stored procedure
which dynamically creates a query like this.

select equipmentid, equipmentname, Manufacturer ,
(select value from equipmentattributes where equipmentid =
a.equipmentid and attributeid = 1) 'Color',
(select value from equipmentattributes where equipmentid =
a.equipmentid and attributeid = 2) 'YearofManufacture'
from equipment a

--Vishal

Quote:>-----Original Message-----
>I have three tables

>1.Equipment

>EquipmentID  EquipmentName     Manufacturer
>1            Aircoditiner      LG
>2            Refregerator      Whirlpool
>3            Router            Cisco

>2. Attributes

>AttributeID AttributeName
>1.          Color
>2.          YearofManufacture

>3. EquipmentAttributes

>EquipmentId   AttributeId  Value
>1                1         White
>1                2         1998
>2                1         Red
>2                2         2000
>3                1         Black
>3                2         2001

>I want to get a result like this

>EquipmentID  EquipmentName     Manufacturer   Color    
YearofManufacture
>1            Aircoditiner      LG             White    
1998
>2            Refregerator      Whirlpool      Red      
2000
>3            Router            Cisco          Black    
2001

>How can i write a query to get a result set like this

connection the above

Quote:>three tables ?
>I cant add the Attributenames to the Equipment table coz
the attribute name
>is a variable. A new attribute can be added later. Please
help

>Thanks in Advance
>Baiju

>.

Hi,
I would do it like that:

Select e.EquipmentID, e.EquipmentName, e.Manufacturer,
ea1.value, ea2.value
From Equipment e
Join EquipmentAttributes ea1
On e.EquipmentID = ea1.EquipmentId
Join EquipmentAttributes ea2
On e.EquipmentID = ea2.EquipmentId
Where ea1.AttributeId = 1
And ea2.AttributeId = 2

in case you must use Attributes table
although we don't need it:

Select e.EquipmentID, e.EquipmentName, e.Manufacturer,
ea1.value, ea2.value
From Equipment e, Attributes a1, Attributes a2
Join EquipmentAttributes ea1
On e.EquipmentID = ea1.EquipmentId
And a1.AttributeId = ea1.AttributeId
Join EquipmentAttributes ea2
On e.EquipmentID = ea2.EquipmentId
And a2.AttributeId = ea2.AttributeId
Where a1.AttributeId = 1
And a2.AttributeId = 2

Quote:>-----Original Message-----
>I have three tables

>1.Equipment

>EquipmentID  EquipmentName     Manufacturer
>1            Aircoditiner      LG
>2            Refregerator      Whirlpool
>3            Router            Cisco

>2. Attributes

>AttributeID AttributeName
>1.          Color
>2.          YearofManufacture

>3. EquipmentAttributes

>EquipmentId   AttributeId  Value
>1                1         White
>1                2         1998
>2                1         Red
>2                2         2000
>3                1         Black
>3                2         2001

>I want to get a result like this

>EquipmentID  EquipmentName     Manufacturer   Color    
YearofManufacture
>1            Aircoditiner      LG             White    
1998
>2            Refregerator      Whirlpool      Red      
2000
>3            Router            Cisco          Black    
2001

>How can i write a query to get a result set like this

connection the above

Quote:>three tables ?
>I cant add the Attributenames to the Equipment table coz
the attribute name
>is a variable. A new attribute can be added later. Please
help

>Thanks in Advance
>Baiju

>.