How eliminate commas in query and get particular id

[Guvera]

Dear friends,

This is my table tblGroups

intGroupId intUserId

1 1,2,3

2 1,3

3 2,3,4



In this table i need to get intGroupId that where intUserId=2. That is "select intGroupId from tblGroups where intUserId=2."

This query is possible to get intGroupId from the above table. I need this one. What query is possible to get the id.

Hope yours reply.

Thanks

Ashok

[jmcilhinney]

That's a very bad schema. For a many:many relationship you should have three tables: User, Group and UserGroup. The UserGroup table has a foreign key from each of the User and Group tables, so that constitutes the relationship. You can then simply query the UserGroup table exactly as you have shown without any need for breaking up a composite value like that.

I'm sure what you're asking for can be done but I don't know offhand how to do it so I'll leave that to others. It will be messy though and, if you had the appropriate schema, unnecessary. I suggest that you change your database schema if it's at all possible.

[Guvera]

Hi dude thanks for your reply.

Yes its correct i got it. You know any best schema for this condition that is Single users in multiple groups .Is it affects performance??

Can you tell that without affect the perfomance we can do this dude.

Thanks

Ashok

[jmcilhinney]

I already told you what the best schema is. Performing a join will always incur a small performance hit but that doesn't mean that you should avoid using multiple tables. Having to break a string up into multiple numbers will affect performance more so you're far better of with the three separate tables where the join table contains nothing but the foreign keys.