Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: IN() Issues
06-03-2011, 11:00 PM #1
- Join Date
- Nov 2009
- Thanked 0 Times in 0 Posts
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?
06-03-2011, 11:06 PM #2
- 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.
06-04-2011, 02:39 AM #3
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.
The reason you tack on the commas is to avoid "false positives".Code:SELECT * FROM links_table WHERE CONCAT( ',' , parent_id, ',' ) LIKE "%,83,%'
For example, if parent_id was '12,83,97' and the searched for value was '8', if you omitted the commas and just did
you would get a false match on the 8 in 83.Code:WHERE parent_id LIKE '%8%'
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: