sql 'in' problem

That does that trick!

I'm going to have to go back and create a pivot table for programs/ages at some point soon, but at least this will get us the correct results in the interim. Lesson learned! ;-)

Thanks Dan!

Actually, the query as you wrote it should crash because you didn't quote 12. Once you get that part out of the way, it should return only the records where ages = 12.

the only way that should work is

where ages like '12,%'
or ages like '%,12,%'
or ages like '%,12'

it gets worse when you want to look for more than 1 number.

I'm having a similar issue. I know that storing a comma-delimited list is bad modeling, but I don't have the option to change the data model at this point.

So, here's some sample data:

Table = Programs

ID......NAME.............AGES
1........Volleyball......11,12,13,14,15
2........Soccer...........10,11,12,13,14,15
3........Baseball........12,13,14
4........Basketball.....12,13,14,15
5........Swimming......9,10,11,12,13,14

and the query:

SELECT ID,Name, Ages
FROM Programs
WHERE Ages IN (12)

but this returns only those records that have "ages" beginning with 12:

3....Baseball.......12,13,14
4....Basketball....12,13,14,15

Should this query be returning all records that contain 12? Is there a better way to match values within a list?

Thanks for any insight!!

Great thanks

I see where I was going wrong now.

You need to fix your data model. You shouldn't store a comma-delimited list, especially if you're going to try to do what you're doing with it.

Make a mapping table, VideoStreamToMovieCat. It has 2 columns, MovieID and MovieCatID.

Now you can do correct joins, etc.

SELECT * FROM videostream JOIN videostreamtomoviecat ON (videostream.movieid = videostreamtomoviecat.movieid) WHERE videostreamtomoviecat.moviecatid = #catid#

For starters, you have your query bassackwards...

SELECT * FROM videostream WHERE videostream.movieCat IN (#catid#)

.. but your query has only one table, not two.

Does this query work any better?

SELECT *
FROM videostream v
WHERE EXISTS(SELECT 1
FROM movicat m
WHERE m.movieCatID = v.movieCat)

Phil