登录
 
主页技术资源库学习下载支持社区论坛
IT 专业人士的资源 >
> 
> 
select on multiple indexes

已答复 select on multiple indexes

  • 2012年7月6日 5:12
     
     
    登录进行投票
    0

    I have 2 tables

    PRODUCTS
    PROD_ID PROD_NAME PROD_CAT
    1 prod1 1
    2 prod2 1,3
    3 prod3 2,3

     

    CATEGORIES
    CAT_ID CAT_NAME
    1 mycat
    2 newcat
    3 thiscat

     

    *each product may be assigned to multiple categories in the PROD_CAT column which are comma seperated.

     

    I need a single SQL Select query to return product ID and NAMES but also the respective CATEGORY NAMES also seperated by comma

     

    example
    1 | prod1 | mycat
    2 | prod2 | mycat,thiscat
    3 | prod3 | newcat,thiscat
    Is this possible? Thanks in advance for any help!
     

全部回复

  • 2012年7月6日 5:17
     
     已答复
    登录进行投票
    0

    I would suggest you to create another table which will contain the Product_ID and the Category_ID, the way you are storing it now is considered very bad in relational terms and if you can't change it, then use a split function to split the comma separated categoryID and then join it with the category table and then again concatenate the category_Name using FOR XML PATH(''). Use will find a lot of examples of doing this on forums.

    Thanks and regards, Rishabh K

    • 已建议为答案 Murali_CHN 2012年7月6日 5:28
    • 已标记为答案 xarrisx 2012年7月6日 11:22
    •  
     
  • 2012年7月6日 11:22
     
     
    登录进行投票
    0

    thanks for the quick reply!

    I think i will have to go with the first solution while it is early in the project!

    I would have bigger problems using the xml data