what's the best SQL syntax

[andrewbadera]

to get a result when:

one table (tblUser) has a many-to-many relationship with other tables (tblClass and tblCategory)?

furthermore, what's the best way to store the relationship in the DB? I had it normalized, with tblUserClass and tblUserCategory illustrating the relationship, but due to the mangled SQL statement I used to return search results, I ended up removing the relationships to tblUser and storing a concatenated and delimitied string containing all class or category IDs in strClassIDs and strCategoryIDs fields.

 

[bperry]

Typically, you need a fourth 'join table' that contains one row for each unique occurence of tblUser.UserId, tblClass.ClassId, and tblCat.CatId.

This new table would look something like:
CREATE TABLE UserClassCat (
UserId int NOT NULL,
ClassId int NOT NULL,
CatId int NOT NULL
Primary Key (UserId, ClassId, CatId)
)

Each of the three columns would be a foreign key pointing back to tblUser, tblClass, and tblCat.

Also, you could add additional columns that are related to each unique row, for example DateCreated, ChargeRate, etc
-----------------
Is this what you were looking for?

bp

 

[andrewbadera]

what's the syntax for retrieving the data? as I said, I had had JOIN tables relating tblUser and tblClass as well as tblUser and tblCategory, but changed it with a hack to retrieve results.

 

[bperry]

Well, I guess it could be something like this, but it really depends on just what query result you want. This is just one (typical) example:

Select *
from tblUser u
Inner Join UserClassCat ucc
ON u.UserId = ucc.UserId
Inner Join tblClass cl
ON ucc.ClassId = cl.ClassId
Inner Join tblCat cat
ON ucc.CatId = cat.CatId
-------------------------------