Query to find a single value from fields containing a list

Thanks all!

Lists are being eradicated as we speak- not too problematic as all data thus far is test data that need to be deleted pre-launch anyway...

I think i need to get reading- thanks again.
KAtie

kicking_katie wrote:
> time is of the essence!

I hear you, but paross1 and I are trying to save you from a world of headaches down the road. Using delimited lists will cause you nothing but problems. Not to mention that your site will be slower because you'll be forced to use WHERE Column LIKE '%...%' in your queries. That will likely prevent the database optimizer from using indexes to improve query performance. That's assuming you can make the queries work at all.

If you choose not to redesign, that's up to you. Do a search on the archives. This topic has come up many times. I don't have any links on hand but I do remember a few recent posts with a kludegy work-around involving LIKE.

kicking_katie wrote:
> so no lists then?

Not if you ever want to efficiently select records based on one value in
such a list, just like your original question concerned. The difficulty
you are experience doing this is exactly why it is considered a poor
practice.

If you absolutely can not|will not normalize your database design you
will have to treat the list as a string and use "LIKE" operations. You
will have to be careful with constructing this LIKE clause so that it
can distinguished a '2' from a '12' and find a value if it is at the
beginning of a list, the middle of a list or end of a list. This will
be about the poorest performance solution available.

(seeing as though my site launches next monday - time is of the essence!)

its cool- think it's the only field with a list in, as luck would have it..

oh i see - thanks cf-Dev for a very constructive answer...

Thank you!

> my db fields contain a list of sections, " 1,2,3,4,"

When you find yourself storing delimited lists in a field, that's a sign that you should normalize your data. Storing lists is difficult to query, error prone and bad for performance.

A separate table should be used to represent a many-to-many relationship.

Table - Columns
Advertisement - AdvertisementID, AdvertisementDescription, ...other columns
Type - TypeID, TypeTitle, ...
AdvertisementType - AdvertisementID, TypeID

Instead of storing the related types as a list
AdvertisementID | TypeID
1 | 1,2,3,4

Store them as separate records in the 3rd table
AdvertisementID, TypeID
1, 1 (ad # 1, type # 1)
1, 2 (ad # 1, type # 2)
1, 3 (ad # 1, type # 3)
1, 4 (ad # 1, type # 4)

oh ok- well i only just changed it...
before it only stored a single numeric value, and now it stores a list...

how should i change it?

(not in the mood for a total database overhaul - its works well enough for me.)

If you have any control over the database design (data model), I would normalize it to remove fields that contain lists of values, since this is a very VERY BAD design.

Phil