This content has been marked as final. Show 4 replies
Normalize your database and you won't have problems like this. If you didn't understand the 1st 3 words, I have heard good things about the book, Database Design for Mere Mortals.
You are right, I have no idea what 'Normalize your database' means! But actually my project is at a state where I don't touch the database design any longer. So unfortunately your input doesn't help much....
But thx anyway
As Dan alluded to, you need to fix your data model. However, you say you can't do this, but you ask: "how do I write the WHERE clause in SQL?"
SQL isn't designed to help you continue to use a faulty data model. Thus, there isn't a nice way to write what you want, and it's going to be dog-slow.
For each item in the list you're searching for (4,90 in your example), you need to check 4 options:
Is this element at the beginning of my list? WHERE row LIKE '#element#,%'
Is this element at the end of my list? WHERE row LIKE '%,#element#'
Is this element in the middle of my list? WHERE row LIKE '%,#element#,%'
Is this the only element in the list? WHERE row = '#element#'
You can see how this gets crazy.
<cfset listToSearchFor = "4,90">
SELECT * FROM tablename WHERE
<cfloop list="#listToSearchFor#" index="idx">
row LIKE '#idx#,%' OR
row LIKE '%,#idx#' OR
row LIKE '%,#idx#,%' OR
row = '#idx#' OR
1 = 2
All of these OR LIKE checks are gonna make the query slower and slower.
In the future, never EVER store a comma-delimited list in a database field. You want to break it out into another table. How do you do that? I'm glad you asked... In your example, where Row 1 = "45,67,2,90,67", assume the primary key for that row is "12"...
Whatever_Table (You'd name this table based on what these ids stand for in the row list)
main_id foreign key references main_table(id)
So your Main_Table would have an entry with an ID of 12 and Whatever_Table would contain:
Now you can get all the whatever_id values for a given main_id by:
select whatever_id from whatever_table where main_id = 12;
And to answer your original question, you can do:
SELECT Main_Table.* FROM Main_Table JOIN Whatever_Table ON (Main.id = Whatever_Table.main_id)
WHERE whatever_id IN (4,90)
Lots easier, huh?
Dear Dan, dear Kronin555
I'm sorry about saying that I wasn't able to fix my data model. After sleeping a night over and dreaming about it, I changed the model as you guys suggested and it works fine.... I didn't understand right away what you meant!
Be sure I will show up again with many stupid questions :-)
So, many thanks for your inputs.