How to get comma delimeted value seperated in join select statement

Question

How to get comma delimeted value seperated in join select statement

Ceema

Newbie

Points: 3
More actions
September 24, 2005 at 12:41 am

#89298

Hello,

Please spend some time to solve my problem. Some people suggests to change my table structure, but I want to avoid it. Because I am so particular in storing data like this because of some other requirement.

I have 3 tables in sql (2000)
1. Category for which fields are CategoryID(int),CategoryName(nvarchar(50))
2. Usercategory, for which the fields are UserID(int),CategoryID(nvarchar(500)),SubcategoryI D(nvarchar(500)).
3. User for which fields are userid,username
In first table values are stored like
Code:
categoryID...............categoryName
1 Cat1
2 cat2
3 Cat3
In 2nd table, I am storing values like
Code:
Userid............... categoryID ...............SubcategoryID
1 ............... 2,4,5,7,9 ............... 8,11,12,14
2 ............... 1 ............... 5,11,8,9
3 ............... 10,12,13 ............... 20,21,22
That means, I am storing using comma seperator. If I create a query
Code:
select categoryname,category.categoryid from category inner join usercategory
on category.categoryid = usercategory.categoryid where usercategory.userid = 1
or
Select CategoryID From Category Where CategoryID In (Select CategoryID From UserCategory Where UserID=1)
then it will not return anything, but if userid is 2 then it's returning 1. Why this problem is coming and how could I sort this out? If it's because of int & nvarchar comparison, how could I convert the item type in query itself, without altering the table structure, becaue now itself, I have a lot of data in my database, with this comma seperator, which I have to retrieve with this query, If I will later the table, then I will have to enter all those again.
NB:- I can use store procedure instead of direct query.
Thanks
Ceema

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Basically, as you rightly mentioned there is no relationship between CategoryID on Category table and CategoryID on userCategory table logically since it is not possible to define a reference on columns of two different datatypes or lengths. Since you are using a inner join between the tables, there is no way that the values in example cited to match so it is not returning any records.

The best way to solve, even though you mentioned you are against doing it is to have cross reference table to save the combinations of the values of both tables.

Thanks

Prasad Bhogadi
www.inforaise.com

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

The "best" way would be to not store data in such a fashion...

That, not-with-standing... something like this should work...

SELECT c.CategoryName,

c.CategoryID

FROM Category c,

UserCategory uc

WHERE ','+uc.CategoryID+',' LIKE '%,'+CAST(c.CategoryID AS NVARCHAR(10))+',%'

AND uc.UserID = 1

Unless you have a very strong language requirement to use NVARCHAR, all you're doing is doubling your storage requirements by not using just VARCHAR.

The reason why data stored in such a fashion as you have is such a no-no is that any query you write (as the example above) can not be made to use an INDEX SEEK... the best you'll ever get out of it is a mere INDEX SCAN which is about 51 times slower. Prasad was correct... if you can normalize the data, you should but do it permanently... not just when you need to do a query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)