I have a database with two tables:
Users has these fields:
UserId unique identifier UserName nvarchar CategoryId int
Categories has these fields:
CategoryId int CategoryName nvarchar
At the moment, every user is in one category. I want to change this so that each user can be in any number of categories. What is the best way to do this?
My site does a lot of really expensive searches, so the solution needs to be as efficient as possible. I don't really want to put the list of categories each user has in a third table, as this means that when I pull back a search, each user will be represented in several rows at once (at least, this is what would happen if I implemented a search with my current, fairly crude, understanding of sql.)
If setting up a many-many relationship, is it possible to return only one row for each user?
DECLARE @SearchUserID nvarchar(200) = 1; SELECT * FROM Users JOIN Categories JOIN CategoriesPerUser WHERE UserId = @SearchUserID
This would return one row for each category the user belonged to. It is possible to have it only return one row?