SQL syntax help required

I have a query which I'm having trouble constructing, and would appreciate any advice or help.

I have two fields I'm populating using a listbox with multiple selection enabled which I'd like to compare and select records where the records have items in common.

eg.

tbl_user
tbl_user.user_group


tbl_article
tbl_article.article_group

tbl_user.user_group has values like 1,3,5,6
tbl_article.article_group has values like 1,3

I thought of using a select statement like;

SELECT * FROM tbl_article WHERE tbl_article.article_group IN tbl_user.user_group

but this doesn't seem to work.

Can anyone help and let me know what I'm doing wrong ?

It's a bit unclear from your explanation, but can the fields from both tables contain multiple, comma-seperated values? Would you please be able to post the entire schema of both tables? Also, are you using Access or SQL Server?

Hi mytch,

Thanks for the reply. The d/b is Access 2000 at the moment, but in the final version I'll probably convert it to SQL2000.

The tables are as follows:

tbl_user
user_id autonumber primary key
user_firstname text 50 not null
user_surname text 50 not null
user_login text 50 not null unique
user_password text 10 not null
user_group text 50 not null
user_bio memo

tbl_group
group_id autonumber primary key
group_group_name text 50 not null

tbl_article
article_id autonumber primary key
article_title text 255 not null
article_body memo not null
article_group text 50 not null

The group table holds the group name which populates the listbox with names instead of numbers.

A user can belong to multiple groups, and articles can be viewed by multiple groups.

The fields in the article and user tables should accept multiple, comma separated values.

Theoretically I want the user to be able to see articles based on group membership, and I want the articles to appear in multiple groups.

For example, if I have three groups in the group table, with values;

1;Friends
2;Clients
3;Administrators

I would like to be able to create an article that will be visible to clients and administrators and hidden from friends.

In the article table, I'd have the value of tbl_article.article_group set to 1,3 for the article record.

I'd allow users to belong to one or more groups.

Hope that makes more sense.

Will.

Hi Will,
Sorry for the delayed reply, been a bit busy

Hmmm CSV'd values in fields is a big no no. What I would've done is create a seperate table for the articles called something like "artCats" and have the structure like this:

articleId
category

so you would have the articleId and the category it exists in. Using this setup you could have unlimited categories for articles. Then it's just a matter of querying the artCats table like this

select * from articles
inner join artCats
on articles.articleId = artCats.articleId
where artCats.category = 3

etc, etc...

As for your current setup, a bit of server side script may work with the LIKE command, however it will be slow.

Thanks for the help, mytch.

I'll create the join table as you suggest, I'm sure it'll do what I need it to.

Cheers,

Will.