Complex Query

[VinceNL]

Hello,

I have a rather complex question which i hope i can make clear.

Consider these tables:

Desc
------
DescID
SectionID
Name (string)
isText (bit)
isNumber (bit)

Field
-----
FielID
ItemID
valText
valNumber

Item
------
ItemID
SectionID

Section
-------
SectionID
Name

As you can see from these tables a section has a name and one or more items. The Item has one or more fields, the valText and valNumber is used depending on the value in the Desc table.

Let's say i have two desc entries, one using valText the other using valNumber.
What i would like is in one record is the field names accoording to the name value in the desc table and the value accoording to the value in the item table depending on the 'isText' and 'isNumber' fields.

What i do now is get all the items, accoording to a particular section and step by step get the values from the field table checking the 'isText' and 'isNumber' fields form the desc table. but i guess this is way too slow.

I guess the problem is the check with the 'isText' and 'isNumber' fields. Could this be solved with subqueries or stored procs?

It's an SQL server 7 DB and i can use stored procs.

Is this possible and if yes, how?


Thanks,
Vince

 

[rajeevnandanmishra]

Hi Vince,
Try this.

select a.itemid, b.fielid, c.name,
case c.isText when 1 then b.valtext
else str(b.valNumber) end as val
from item a, field b, desc c
WHERE a.sectionid=mysectionid and
a.sectionid=c.sectionid and
a.itemid=b.itemid and b.fielid=c.descid

In this example the numeric value is converted to a string. As a single column can not contain both (the numeric & character) values.

Hope this will move you in right direction.