Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
boyceofreason
Starting Member
1 Post |
 Posted - 2006-05-15 : 17:28:09
|
| I was wondering if I could get some help regarding a small issue I've cornered myself into. Let me simplify:Companies (table) Company_idCompany_nameCategory_idsCategories (table)category_idcategoryI've stored a comma delimited list of category ids in the Companies.Category_ids field, and I need to make a list of companies that are associated with a particular category. Yet as I'm sure you know, when I use like '%foo' I'll get incorrect data. For instance if I only need category_id of 1 and use 'like' to search 1,23,41,53,11 it will return 1,41, and 11. I know I could make an xref table, but I'd like to get around that using an sql statement. Any thoughts? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-15 : 17:59:09
|
You should change your database to a normalized design by creating a junction table to model the many-to-many relationship between Companies and Categories, and then adding foreign keys to that table to reference the primary keys of the Companies and Categories tables.The issue you have already is just a symptom of the design error. You will have many more if you don't correct the design.CompanyCategories(Company_idcategory_id) CODO ERGO SUM |
 |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-05-15 : 18:58:24
|
| If you put a comma at the beginning and end of your string you can change your like clause to LIKE '%,1,%' |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Searching Comma Delimited Varchar