Complex SELECT QUERY using look-up tables

Hi gang,

I'm trying to find the best way to get a SELECT query to return field values for a table that are stored in another lookup table. Here's a basic example that will illustrate what I'm trying to do.

Assume three tables: tblItem, tblCustomFieldNames, tblCustomFieldValues.

The schemas/columns for the tables are as follows:

tblItem
id
itemName

tblCustomFieldName
id
customFieldName

tblCustomFieldValue
id
customFieldValue
customFieldID
itemID references tblItem(id)

Further assume, that the tables contain the following data:

tblItem
id|itemName
(1,CPU)
(2,Motherboard)

tblCustomFieldName
id|customFieldName
(1,Manufacturer)
(2,Price)
(3,Qty)

tblCustomFieldValue
id|customFieldValue|customFieldID|itemID
(1,AMD,1,1)
(2,$99,2,1)
(3,2,3,1)
(4,ASUS,1,2)
(5,$79,2,2)
(6,1,3,2)

My question is what does my SQL "SELECT query" syntax need to be such that I am able to return a result with the following form:

tblItem.id|Manufacturer|Price|Qty
(1, Intel, $99, 2)
(2, ASUS, $79, 1)

Note: It's not an option for me to re-design the database schema, as it's someone else's database. I simply need to be able to obtain the above resultset using a single SELECT query.

Thanks in advance! :)
-EHere you are:SELECT id, MAX(manufacturer) manufacturer, MAX(price) price, MAX(qty) qty
FROM
(
SELECT i.id,
CASE n.id
WHEN 1 THEN v.customfieldvalue
ELSE NULL
END manufacturer ,
CASE n.id
WHEN 2 THEN v.customfieldvalue
ELSE NULL
END price,
CASE n.id
WHEN 3 THEN v.customfieldvalue
ELSE NULL
END qty
FROM TBLITEM i, TBLCUSTOMFIELDNAME n, TBLCUSTOMFIELDVALUE v
WHERE v.customfieldid = n.id
AND i.id = v.itemid
)
GROUP BY id;