View Full Version : IN() Issues

06-03-2011, 11:00 PM
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
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.

Old Pedant
06-04-2011, 02:39 AM
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.