Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Posts
    1

    Unanswered: 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!
    -E

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •