Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: IN() Issues

  1. #1
    New Coder
    Join Date
    Nov 2009
    Thanked 0 Times in 0 Posts

    Question IN() Issues

    I'm creating a link directory database where links can belong to multiple categories. A link will have an INT id field as PRIMARY KEY and a VARCHAR parent_d field with a comma separated list of category ids (12,3,45,etc) that it belongs to.

    So if I want to find all links that belong to category 83 I use

    SELECT * FROM links_table WHERE 83 IN(parent_id)

    But this doesn't work if the parent_id field contains more than one value. if parent_id = "83" then it works. If parent_id = "83,12" the sql query doesn't find it. What am I doing wrong?

  • #2
    Rockstar Coder
    Join Date
    Jun 2002
    Thanked 328 Times in 324 Posts
    Your data isn't normalized that is what is wrong. You shouldn't store more than one piece of information in a field otherwise you run into issues exactly like this.

    What you need to do is create a new table that connects a link with a category. There would be one row for each category that a given link is in. This is a called a one to many relationship.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,740 Times in 4,702 Posts
    OracleGuy is of course 100% correct. A DB redesign is *desperately* called for.

    But if you are stuck with the design because somebody else created it, you *can* do this.

    It's just uglier than pig snot.
    SELECT * FROM links_table 
    WHERE CONCAT( ',' , parent_id, ',' ) LIKE "%,83,%'
    The reason you tack on the commas is to avoid "false positives".

    For example, if parent_id was '12,83,97' and the searched for value was '8', if you omitted the commas and just did
    WHERE parent_id LIKE '%8%'
    you would get a false match on the 8 in 83.

    By using the commas, that won't happen.

    But *DO* try to redesign this DB yesterday, if at all possible.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    bonecone (06-04-2011)


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts