Very Computer

Hi,

I need to know whether question 5) below can be answered via SQL syntax, or
do I have to write a program for this?
(Please use fixed font for point 3)

1)
I have a table with three columns:  ObjectKey, ObjectAttribute,
AttributeValue.
2)
Primary Key on the Table:  Composite - (ObjectKey and ObjectAttribute).
Business Logic:  An object can have zero, 1 or more attributes.  An object's
attribute can only have one value.
(E.g. Object = The Postman; ObjectAttribute = Sex;  AttributeValue = M)
3)
Sample Values:
ObjectKey     ObjectAttribute    AttributeValue
FOO1          LENGTH             30
FOO1          BREADTH            20
FOO1          LOCATION           Plot X
FOO2          LENGTH             40
FOO2          BREADTH            30
FOO3          LOCATION           Plot Y
FOO3          COLOUR             Brown
4)
Business Logic of the table (reason of existence!):  This table will cater
for all sundry attributes of the "objects" - we don't know how many
different attributes exist for these objects, so we need to keep a "dynamic"
table that will allow us to enter new attributes of the objects whenever
they become known.
5)
I need to answer the following business question in SQL syntax (if it is
possible):
"List all Objects that have a Length of 30 and Breadth of 20."
6)
I am using SQL 6.5 and ADO  2.1.  However, I want my solution to be DB
independent (ie.  I want to solve this business question with a SQL
statement)

Anyone?

Regards

A Kauchali
Johannesburg
South Africa

SELECT d1.ObjectKey
FROM mytable d1 INNER JOIN
    mytable d2 ON d1.ObjectKey  = d2.ObjectKey
WHERE d1.ObjectAttribute = 'length' AND d1.AttributeValue = '30' AND
    d2.ObjectAttribute = 'breadth' AND d2.AttributeValue = '20'

Someone will probably post  a better solution.

Regards,
farhan

Quote:

> Hi,

> I need to know whether question 5) below can be answered via SQL syntax,
or
> do I have to write a program for this?
> (Please use fixed font for point 3)

> 1)
> I have a table with three columns:  ObjectKey, ObjectAttribute,
> AttributeValue.
> 2)
> Primary Key on the Table:  Composite - (ObjectKey and ObjectAttribute).
> Business Logic:  An object can have zero, 1 or more attributes.  An
object's
> attribute can only have one value.
> (E.g. Object = The Postman; ObjectAttribute = Sex;  AttributeValue = M)
> 3)
> Sample Values:
> ObjectKey     ObjectAttribute    AttributeValue
> FOO1          LENGTH             30
> FOO1          BREADTH            20
> FOO1          LOCATION           Plot X
> FOO2          LENGTH             40
> FOO2          BREADTH            30
> FOO3          LOCATION           Plot Y
> FOO3          COLOUR             Brown
> 4)
> Business Logic of the table (reason of existence!):  This table will cater
> for all sundry attributes of the "objects" - we don't know how many
> different attributes exist for these objects, so we need to keep a
"dynamic"
> table that will allow us to enter new attributes of the objects whenever
> they become known.
> 5)
> I need to answer the following business question in SQL syntax (if it is
> possible):
> "List all Objects that have a Length of 30 and Breadth of 20."
> 6)
> I am using SQL 6.5 and ADO  2.1.  However, I want my solution to be DB
> independent (ie.  I want to solve this business question with a SQL
> statement)

> Anyone?

> Regards

> A Kauchali
> Johannesburg
> South Africa

Abdullah,

Quote:>I need to know whether question 5) below can be answered via SQL syntax, or
>do I have to write a program for this?

The solution Farhan posted is fine when there are two tests, but can
get a bit awkward when the number of tests goes up.

One approach I might take is much simpler if we have an Objects table,
keyed on ObjectKey, so I will assume one exists.

SELECT *
  FROM Objects as O
 WHERE EXISTS (select * from ObjectAttributes as OA1
                where O.ObjectKey = OA1.ObjectKey
                  and OA1.ObjectAttribute = 'Length'
                  and OA1.AttributeValue = '30')
   AND EXISTS (select * from ObjectAttributes as OA2
                where O.ObjectKey = OA2.ObjectKey
                  and OA2.ObjectAttribute = 'Breadth'
                  and OA2.AttributeValue = '20')

Or, alternately:

SELECT ObjectKey
  FROM Objects
 WHERE (ObjectAttribute = 'Length'  and AttributeValue = '30')
    OR (ObjectAttribute = 'Breadth' and AttributeValue = '20')
 GROUP BY ObjectKey
HAVING count(*) = 2

where the HAVING clause lists how many alternatives are being tested.

Roy