Write a complex query connecting three tables

Write a complex query connecting three tables

Post by Baiju K » Fri, 14 Mar 2003 21:31:42



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

 
 
 

Write a complex query connecting three tables

Post by Vishal Parka » Fri, 14 Mar 2003 21:56:03


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

- Show quoted text -

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

>.


 
 
 

Write a complex query connecting three tables

Post by Arik » Fri, 14 Mar 2003 22:27:58


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

- Show quoted text -

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

>.

 
 
 

1. connecting three tables???

I have a problem in a way similar to the example DBERRORS.PRJ in
Delphi (I have Delphi 2), which connects Customers , Orders  and
Items . Let's say that I have 3  tables as in the example but I have
to show all items  ( from every order) from table Items connected with
a specific customer . I will not display orders at all.
I  could do this with  Tquery object  and SQL statement , something
like:

select TOrder.Qty, TOrder.Price, TItem.Item, TItemPrice from TOrder,
TItem where TOrder.IDOrder= TItem.IDOrder and TOrder.IDCustomer =
:IDCustomer

but then I can't edit and modify the result, and I have to do this.
This could be simple to you Delphi masters out there but  I came to
standstill with it. Restructuring of tables is the last thing I can do
'cos I am trying to build an application over existing Access
application.
Thanks in advance  
Gordan

2. ALPHA FOUR COMPETITIVE U

3. Advice on indexes for complex Select query with very complex Where clause

4. Informix NT vs Unix

5. three table query

6. inconsistent results

7. query to find three max values from table

8. Handling user logins

9. Three Table Query Problem

10. One query, Three tables and permissions.

11. Help with query to count across three tables

12. A Question about a query on three tables