Usage of the " IN " operator

[Conti]

Permits contains a string: 4,16,12,9,12,7,5 .....
cookie.userid is a number

<cfquery name="rsTitols" datasource="gallery">
SELECT * FROM Galleries
WHERE '#cookie.userid#' in(SELECT permits from Galleries)
</cfquery>

does not produce an error but doesn't return any value.
Should value in "permits" be quote delimited with ListQualify()?
How do I do that?

Thank you very much.

[paross1]

Very bad data model/design. Is there any way that you could avoid having a comma delimited list in the permits field?

Phil

[Conti]

You are right Phil. The newbie here.

At creation, I need to relate a record in table Galleries to a certain number
of users in table Users.
Basics I suppose. Could you give me an hint.

Thnak you very much.
Wal

[paross1]

So I am assuming that there is a many-to-many relationship between Galleries
and users. (More than one user can be associated with more than one gallery,
and vice versa.) You should create a "link" table (associative entity) that
contains, at a minimum, the PK field of the Galleries table and the PK field of
the users table (I am assuming that Galleries has a primary key). Call this
GalleryUsers. Then for every case where you have a user associated to a
gallery, you would insert a row with the userID and galleryID. Then you could
join Galleries, Users, and GalleryUsers in a query to select users associated
with a gallery, or galleries associated with a particular user, or all
galleries and users, etc.

For example, something like this would allow you to list the galleries that
are associated with a particuar user:

SELECT g.gallery_name
FROM users u, Galleries g, GalleryUsers gu
WHERE gu.galleryID = g.galleryID
AND gu.userID = u.userID
AND u.user_name = '#UserName#'

Phil