Results 1 to 2 of 2
Jan 12, 2011, 17:27 #1
- Join Date
- May 2003
- Sarasota, FL
- 0 Post(s)
- 0 Thread(s)
How is a comma-separated value from a subquery interpreted in an IN() clause?
I have a table called system_vars that stores among other things a comma-separated list of integers - '66,55,44,33' etc.
If I use that field in a subquery inside of an IN() statement, is there a way to have the result interpreted as individual integers instead of a single string?
SELECT * FROM `products` WHERE category_id NOT IN(SELECT sys_val FROM system_vars WHERE sys_var = 'excluded_cats');
* Please do not say "Normalize the table". I know it's not the best design, but it's what I was handed and changing it is not possible at the moment.
EDIT: But I would be OK if the solution involved creating a normalized temp table. I just wasn't sure how to split the value using only MySQL commands.
PS: The final solution needs to be usable in a view.
Last edited by xangelusx; Jan 12, 2011 at 17:29. Reason: clarificationChris Bloom
Web Application Developer
Jan 12, 2011, 19:09 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 52 Post(s)
- 2 Thread(s)
you can split the data in two ways, one of them very clumsy using CONCAT with commas and LIKE with commas, the other relatively clean using the FIND_IN_SET function
but both of those methods do not scale, as they always require a table scan
normalizing the data would result in searches that are optimized because they can use an index
table scans are grossly inefficient
anyhow, you asked me not to tell you to normalize, so i won't