Complex SELECT QUERY using look-up tables

Everbored · #1 (**permalink**) 11-18-06, 20:33

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!

-E

Littlefoot · #2 (**permalink**) 11-21-06, 07:32

Here you are:

Code:

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech