>>>but one item has entries of 15,16,30,49,50,52,75 and it is being put in
>>>a catecory that has the id of 2 there are lots of these that are
>>>behaving this way. any one think of a way to help with this?
> > SELECT catid FROM deep_images WHERE #uid# IN (catid)
> IN is evil.
> It will randomly stop working when you hit the db's limit for items in a
> string list.
> Which is not fun :-)
>
> If you must have comma lists rather than a more sensible layout, why not just:
> SELECT catid
> FROM deep_images
> WHERE #uid# like '%#catid#%'
>
> % should match zero or more characters.
Yes, IN is not an ideal situation, and certainly hurts performance.
However, using like does not solve his original problem. In your
scenario, that would translate to (using Patrick's own example):
SELECT catid
FROM deep_images
WHERE 2 like '%15,16,30,49,50,52,75%'
(which just doesn't work at all)
But, if you use IN, it _can_ work as expected (if you are passing in
the list value):
SELECT catid
FROM deep_images
WHERE 2 IN (15,16,30,49,50,52,75)
returns no record, since 2 isn't in the set. But if you change #uid#
to 52, it would return the record since 52 is in the set. This works
whether or not the set is quoted. So
SELECT 1
FROM invoice
WHERE 2 in ('15','16','30','49','50','52','75')
works the same way as the previous IN shown above.
Unfortunately, at least in SQL Server, running the above with the list
being a reference to a database column will produce:
"Syntax error converting the varchar value '15,16,30,49,50,52,75' to a
column of data type int."
Going with a linking table is probably the right way to go. Better
performance too.
Cheers,
Kris
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248814
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
