Q:

SQL statement - how to use multiple CASE with different SELECT?

I'm pretty new with SQL, and I'm using a PHP Generator software for Rapid application development.
I have a dropdown to which I need to apply a SQL statement for the lookup, to populate the dropdown.
If this was Javascript it would be easy, but not sure how to proceed with SQL.
My table has items with different types, the types of each item can be:
'P'
'B'
'O'
'S'

when I open an Item, it loads its informations, and I need to populate this dropdown based on the Item type.
The dropdown in my Table is a Tinyint.
For the selected Item, I have the ItemID (PK in the Table) in a global variable, from there, to retrieve the Type of that item, a simple SELECT should work:
SELECT ItemType
FROM TblItem
WHERE ItemID = [ItemID]

so with this SELECT now I have my ItemType.
From there, now I need to populate my dropdown based on the ItemType
With Javascript I would do something like:
switch(ItemType) {
  case 'B':
    // code block
    break;
  case 'P':
    // code block
    break;
 case 'S':
    // code block
    break;
 case 'O':
    // code block
    break;
}

and for each CASE code block, I would put a different SELECT statement to populate my dropdown, for example:
case 'B':
  SELECT 0 AS TaskID, 'Installation' AS TaskName
  UNION
  SELECT 1 AS TaskID, 'Setup' AS TaskName
  UNION
  SELECT 2 AS TaskID, 'Feedback' AS TaskName
  UNION
  SELECT 3 AS TaskID, 'Customer' AS TaskName

case 'P':
  SELECT 4 AS TaskID, 'Production' AS TaskName
  UNION
  SELECT 5 AS TaskID, 'Debug' AS TaskName
  UNION
  SELECT 6 AS TaskID, 'Review' AS TaskName
  UNION
  SELECT 7 AS TaskID, 'Network' AS TaskName

and so on, so each time I open the details view of an Item, I retrieve the ItemID in a global variable, then I should use this ID to retrieve the ItemType as shown above, and finally, implement some kind of CASE statement in SQL to fill the dropdown based on the Type
thank you

A:

You can have a trick with INNER JOIN, put all possible records of dropdown in a table as I called it tblDropDownItems with an extra column refer to ItemType, when you join this table with filtered table TblItem, you just have the related records to the retrieved item type:  
SELECT tblDropDownItems.TaskID, tblDropDownItems.TaskName
FROM
    (SELECT 0 AS TaskID, 'Installation' AS TaskName, 'B' AS ItemType
     UNION
     SELECT 1 AS TaskID, 'Setup' AS TaskName, 'B' AS ItemType
     UNION
     SELECT 2 AS TaskID, 'Feedback' AS TaskName, 'B' AS ItemType
     UNION
     SELECT 3 AS TaskID, 'Customer' AS TaskName, 'B' AS ItemType
     UNION
     SELECT 4 AS TaskID, 'Production' AS TaskName, 'P' AS ItemType
     UNION
     SELECT 5 AS TaskID, 'Debug' AS TaskName, 'P' AS ItemType
     UNION
     SELECT 6 AS TaskID, 'Review' AS TaskName, 'P' AS ItemType
     UNION
     SELECT 7 AS TaskID, 'Network' AS TaskName, 'P' AS ItemType
     UNION
     . . . your select clause for 'S' ItemType
     UNION
     . . . your select clause for 'O' ItemType) tblDropDownItems
INNER JOIN
     (SELECT ItemType
      FROM TblItem
      WHERE ItemID = [ItemID]) tblItemType
ON tblDropDownItems.ItemType = tblItemType.ItemType

