Skip to main content
I
imadnan
Participant
December 4, 2010
Answered

QUERY : comma separated checkbox vlues in category

  • December 4, 2010
  • 1 reply
  • 417 views

Id      Name      Category ( comma separated checkbox vlues in category )

001     abc           1,2

002     xyz           2,3

003     zzz           0,1

QUESTION:

I want to get recoreds of those category which have category 1 and 0 ..

Select   *

FROM  Table

Where  ?

Thank,

    This topic has been closed for replies.
    Correct answer JMF3

    Should normalize to have a mapping table instead:

    Map

    ==========

    Id Category

    001 1

    001 2

    002 2

    002 3

    003 0

    003 1

    Then you use a JOIN to get the matching records:

    SELECT Table.*

    FROM Table INNER JOIN

    Map ON Table.Id = Map.Id

    WHERE Map.Category IN ( 0, 1 )

    To do it with your current data model, you have to do something like this:

    SELECT *

    FROM Table

    WHERE ',' + Category + ',' LIKE '%,0,%'

    OR ',' + Category + ',' LIKE '%,1,%'

    1 reply

    J
    JMF3Correct answer
    Participating Frequently
    December 4, 2010

    Should normalize to have a mapping table instead:

    Map

    ==========

    Id Category

    001 1

    001 2

    002 2

    002 3

    003 0

    003 1

    Then you use a JOIN to get the matching records:

    SELECT Table.*

    FROM Table INNER JOIN

    Map ON Table.Id = Map.Id

    WHERE Map.Category IN ( 0, 1 )

    To do it with your current data model, you have to do something like this:

    SELECT *

    FROM Table

    WHERE ',' + Category + ',' LIKE '%,0,%'

    OR ',' + Category + ',' LIKE '%,1,%'

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices