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:
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):
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