Hello and welcome to our community! Is this your first visit?
Register
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
    Posts
    53
    Thanks
    3
    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
    Location
    USA
    Posts
    9,074
    Thanks
    1
    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.
    OracleGuy

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 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.
    Code:
    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
    Code:
    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
    •