query using commas

Hi,

I have table Article(ID,Title,FAID)

I need a query that will select all the Article.ID records where the FAID contains the number of the article ID

For exapmle, Article Table content is:

1, "Title1","2,6"2, "Title2",""3, "Title3","6,1"

6, "Title6","2"

Lets say I want to get all titles of article ID 1. I am going to its FAID which is "2,6"

So the query will return : "Title2", "Title6"

Can you advice how to write this? I can do a walk around solution where I will open a new table nameFAbut I rather not to.

You could use the CHARINDEX function in T-SQL. Something like this:

WHERE CHARINDEX(FAID, ID) > 0

This is untested, but will probably work for you.

BUT, this looks an awful lot like a non-normalized table, since you have multiple items in the FAID field. You're likely to be able to write much cleaner and more efficient queries if you normalize it.

Don

|||

the FAID fiels containd a related article IDs. this mean I will use this field only in one query. the one that I am building right now.

although at the moment I have normlized table ReleatedArticle (FAID,ArticleID). for each FAID, I have multiple ArticleIDs, and this is normlize.

do you think I should leave it like this normlized, and not to change to the CHARINDEX solution?

I don't like to open a new table when it seems like unneccessary one. please advice.

|||

Well, the normalization rules are not absolute, although there are people who treat them as such. Sometimes there are good reasons to break the rules. If you truly will not use the information in any other way, in any other queries, this might possibly be efficient for you. But if you ever find yourself writing any convoluted T-SQL or client code to work with the related article ID data, consider putting it into a separate table.

Don