SQL Select Row from other table within Select query

Hi all,

Sorry for the low points on this one it's all I have remaining.

I have 3 types of categories: Category, SubCategory and MiniCategory as individual tables

I have one product table which can be related to up to three categories and these could be any of the three categories.

The products table:

ProductID
Title
.......
FirstCategoryID
SecondCategoryID
ThirdCategoryID
FirstCategoryType
SecondCategoryType
ThirdCategoryType

The *categoryID row contains the category ID and the *categoryType contains either 'cat', 'sub' or 'mini' depending which category table is used.

I was toying with the Idea of just having one category table and have rows determining to say if the entry is a top category, sub or mini but that doesn't seem very relational.

So my problem is as follows:

I'm trying to create a View and retrieve the category name for each product and any of the three categories the product could be listed in. So for example pseudo (This doesn't work):

SELECT     prods.*,
CASE prods.FirstCategoryType
 WHEN prods.FirstCategoryType = 'cats' THEN (SELECT CategoryName from Comp_Categories WHERE Comp_Categories.CategoryID = prods.FirstCategoryID)
 WHEN prods.FirstCategoryType = 'sub' THEN (SELECT SubCategoryName from Comp_SubCategories WHERE Comp_SubCategories.SubCategoryID = prods.FirstCategoryID)
 WHEN prods.FirstCategoryType = 'mini' THEN (SELECT MiniCategoryName from Comp_MiniCategories WHERE Comp_MiniCategories.MiniCategoryID = prods.FirstCategoryID)
END AS 'FirstCategoryName',

..... And so on for SecondCategoryName and ThirdCategoryName

FROM   dbo.Comp_Products prods

I have tried many variations and also IF statements and joining etc but to no avail. Maybe I need to simplify this somehow.

This is part of a VS C#.NET project so am using SQLExpress that shipped with VS 2008, I don't know which version of SQLExpress I have but any code must be compatible with SQL Server 2005.

Thank you, Craig