Newt6398
asked on
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.SubCate goryID = prods.FirstCategoryID)
WHEN prods.FirstCategoryType = 'mini' THEN (SELECT MiniCategoryName from Comp_MiniCategories WHERE Comp_MiniCategories.MiniCa tegoryID = 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
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
WHEN prods.FirstCategoryType = 'sub' THEN (SELECT SubCategoryName from Comp_SubCategories WHERE Comp_SubCategories.SubCate
WHEN prods.FirstCategoryType = 'mini' THEN (SELECT MiniCategoryName from Comp_MiniCategories WHERE Comp_MiniCategories.MiniCa
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Missed this in first post but verify you have the correct values for the CatetoryType codes.
ASKER
dan_neal your solution worked perfectly for the original problem using the case statement. However upon reflection angellll's solution is the way to go to keep a good relational database.
Thanks to you both,
Craig
Thanks to you both,
Craig