none
select on multiple indexes

    问题

  • 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:12

答案

  • 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日 5:17

全部回复

  • 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日 5:17
  • 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

    2012年7月6日 11:22