sql 'in' problem

[Mattastic]

Hi,

I have 2 tables(below), I need to pull out all the rows in the videostream table where the videostream.movieCat list contains the ID of the category in the moviecat table. Normally I would just use the IN query:

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

However I cant get his to work, can anyone tell me the best way to go about this?

Thankyou


movicat
movieCatID int
movieCatName nvarchar

Videostream
movieID int
movieFilename ntext
movietitle nvarchar
movieCat nvarchar
movieSubtitle nvarchar
movieEpisode int
movieduration int
moviekeywords ntext
moviebroadcaster int
moviebroadcastdate smalldatetime
movieLicense nvarchar
movieDescription ntext

[paross1]

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

[Mattastic]

Hi Phil,

Thanks for your reply.

I get this error when I use your query:

Syntax error converting the nvarchar value '3,1,2' to a column of data type int.

I just need to pull out all the rows that are in the list '3,1,2'

Sorry for the poor explanation, its really confusing me

[paross1]

quote:

---

I get this error when I use your query

---

Which one?

Phil

[Kronin555]

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#

[Mattastic]

Great thanks

I see where I was going wrong now.

[Guest]

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!!

[Dan_Bracuk]

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.

[Guest]

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!