jumpseatnews
asked on
How to set up multiple categories in MS SQL Server?
This may be a simple question, so forgive me in advance, but how do you assign multiple categories in a single field for SQL Server data?
Example, I have in one table:
ID, int (PK)
FirstName, varchar
LastName, varchar
EmployeeNumber, int
DatePosted, smalldatetime
CategoryID, int (FK to a CategoryID table)
Then, in the other CategoryID table, I have:
CategoryID, int (PK)
Category, varchar
How do I assign on record in the first table to have multiple categories associated with it?
Thus, say the record is:
John
Smith
#28391
8/14/2007
[here i want to have 'Admin, User, Writer' in associated to this.
The other table would be:
1 Admin
2 User
3 Power User
4 Writer
5 Author
etc...
Do I just put 1,2,4 in the one area? I'm sure there is an easy methodology for this, but I'm not getting the best way tot do this without having to crate a boolian set of columns for each category in the primary table.
This data will eventually be used to display on an ASP.NET page.
Chris
Example, I have in one table:
ID, int (PK)
FirstName, varchar
LastName, varchar
EmployeeNumber, int
DatePosted, smalldatetime
CategoryID, int (FK to a CategoryID table)
Then, in the other CategoryID table, I have:
CategoryID, int (PK)
Category, varchar
How do I assign on record in the first table to have multiple categories associated with it?
Thus, say the record is:
John
Smith
#28391
8/14/2007
[here i want to have 'Admin, User, Writer' in associated to this.
The other table would be:
1 Admin
2 User
3 Power User
4 Writer
5 Author
etc...
Do I just put 1,2,4 in the one area? I'm sure there is an easy methodology for this, but I'm not getting the best way tot do this without having to crate a boolian set of columns for each category in the primary table.
This data will eventually be used to display on an ASP.NET page.
Chris
For clarification I'm calling ID in your first table UserID in my example. You should give your ID's a name to go with it for when you create a table, like you did for categories.
ASKER
So let's say I have the following:
tblUserTable
ID, int (PK)
FirstName, varchar
LastName, varchar
EmployeeNumber, int
DatePosted, smalldatetime
CategoryID, int (FK to a CategoryID table)
tblCategoryID
CategoryID, int (PK)
Category, varchar
You're saying that there should be a third table that combines the first two above?
It would be:
tblUserCategory
UserCategoryID, int (PK)
UserID, int (FK to tblUserTable)
CategoryID, int (FK to tblCategoryID)
So, then suppose then that you want to query all users that are Admins, Users, and Writers. Can this be performed with this setup? What would be a simply example of how to generate that kind of select query with that structure?
tblUserTable
ID, int (PK)
FirstName, varchar
LastName, varchar
EmployeeNumber, int
DatePosted, smalldatetime
CategoryID, int (FK to a CategoryID table)
tblCategoryID
CategoryID, int (PK)
Category, varchar
You're saying that there should be a third table that combines the first two above?
It would be:
tblUserCategory
UserCategoryID, int (PK)
UserID, int (FK to tblUserTable)
CategoryID, int (FK to tblCategoryID)
So, then suppose then that you want to query all users that are Admins, Users, and Writers. Can this be performed with this setup? What would be a simply example of how to generate that kind of select query with that structure?
Yea, you will need a 3rd table.
You would need to do a Join in your queries.
Here is an example query:
SELECT tblUsers.* FROM tblUsers
INNER JOIN tblUserCategory ON tblUserCategory.UserID = tblUsers.UserID
INNER JOIN tblCategory ON tblCategory.CategoryID = tblUserCategory.CategoryID
WHERE tblCategory.Category = 'Admins' AND
tblCategory.Category = 'Users' AND
tblCategory.Category = 'Writers'
You would need to do a Join in your queries.
Here is an example query:
SELECT tblUsers.* FROM tblUsers
INNER JOIN tblUserCategory ON tblUserCategory.UserID = tblUsers.UserID
INNER JOIN tblCategory ON tblCategory.CategoryID = tblUserCategory.CategoryID
WHERE tblCategory.Category = 'Admins' AND
tblCategory.Category = 'Users' AND
tblCategory.Category = 'Writers'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank YOU! This is great. My only other question is on how would i make an insert statement for this same query above. However, I will post it now as a new question, as you more than answered this one.
Chris
Chris
No need just create a separate INSERT statement for each table.
tblUserCategory:
UserCategoryID, int (PK)
UserID, int
CategoryID, int