• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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
0
Newt6398
Asked:
Newt6398
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think the design should be different.

if you really need 3 different tables for special categories, do that, but keep the id + name (+type) in 1 single main category table


CATEGORY_LIST
 category_id (identity, primary key)
 category_type
 category_name

CATEGORY_MAIN (previously Category)
 id  (foreign key to category_list->category_id)
 + any special fields for category table

CATEGORY_SUB (previously SubCategory)
 id  (foreign key to category_list->category_id)
 + any special fields for Subcategory table

CATEGORY_MINI (previously MiniCategory)
 id  (foreign key to category_list->category_id)
 + any special fields for minicategory table


also, your product table should NOT have 2x3 fields for the relation, but instead you should have a Product_Categories table for the many-many relation.

Product_Categories
 Product_ID (foreign key -> Products.Product_ID
 Category_ID (foreign key -> Category_list.Category_ID
 => not category_type field here, as it is already defined in the category_main table !!

the queries shall become very simple, now.

0
 
dan_nealCommented:
I believe its simply you have your CASE statement structured incorrectly.
SELECT     prods.*,
CASE prods.FirstCategoryType
 WHEN 'cats' THEN (SELECT CategoryName from Comp_Categories WHERE Comp_Categories.CategoryID = prods.FirstCategoryID)
 WHEN 'sub' THEN (SELECT SubCategoryName from Comp_SubCategories WHERE Comp_SubCategories.SubCategoryID = prods.FirstCategoryID)
 WHEN 'mini' THEN (SELECT MiniCategoryName from Comp_MiniCategories WHERE Comp_MiniCategories.MiniCategoryID = prods.FirstCategoryID)
END AS 'FirstCategoryName',

Open in new window

0
 
dan_nealCommented:
Missed this in first post but verify you have the correct values for the CatetoryType codes.
0
 
Newt6398Author Commented:
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
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now